Should varchar or datetime be used for the field type of date stored in the database ?, Varchardatetime

Should varchar or datetime be used for the field type of date stored in the database ?, Varchardatetime

Background:

Some time ago, I saw an article "how to show your (lover) name in the lower right corner of my computer" in Baidu experience. I have also heard of this tip before, but I have not really set it, so I tried it out of curiosity.

The effect after setting is as follows. my name "Danny" is added to the time area in the lower right corner ":

The above is the background. I did not expect this tips to bring me trouble (of course, it is also a learning and improvement opportunity ).

This string is not recognized as a pseudo-valid DateTime

In a news publishing system, the field type of the stored time in the database is datetime, and the Field Values are automatically obtained on the server side. A problem occurs when the client displays the time in the format of "yyyy-MM-dd HH: mm: ss": "The string is not recognized as a pseudo-valid DateTime ":

The error page is shown in the following figure:

Key error code:

LblCreateTime. text = Convert. toDateTime (news. createTime ). toString (); // [note]: lblCreate is a Lable on the front-end display page; news is the "news" entity class obtained after query, and CreateTime is a field of it.

I guess this is a problem with the time format of my local computer. I obtained the time news on the client. the value of CreateTime, in the format of "12:42:10", and the value stored in the database for this record is "12:42:10 ". After testing, if the type of news. CreateTime stored in the database is varchar (), this error will not occur. As you can see, the time format conversion process is as follows:

In this process, the system determines that the value obtained from the database is of the datetime type, therefore, you need to obtain the value (for example, the time value obtained from the database is "12:42:10 ") convert to the local time format (for example, the time format of my computer is "12:42:10" on Saturday Danny). During the last step of format conversion, the system reports an error because it cannot identify the custom time format (for example, "12:42:10 Danny on Saturday.

I have found two articles on the Internet that summarize the time format conversion in Asp.net: several methods for formatting time and date in asp.net. In general, I divide these methods into two methods: converting the interface code (*. aspx) and converting the background code (*. aspx. cs.

Solution

There are two solutions to this problem:

1. If the data type of the stored time in the database is datetime, avoid the background code (*. aspx. cs) to convert the time format and put the format conversion task into the interface code (*. aspx;

For example, in the above example, No matter what format the obtained time is, do not perform any operations on the value of this time in the background (such as assigning values, otherwise the system will implicitly convert the time ), instead, directly go to the interface code (*. aspx) Use DataBinder, Eval, and other methods for direct formatting:

Key background code:

<Span style = "white-space: pre"> </span> DataTable dt = new NewsManager (). selectById (newsid); // here, dt is the data queried from the direct database <span style = "white-space: pre"> </span> repNews. dataSource = dt; repNews. dataBind ();

Key front-end code:

<Span style = "white-space: pre"> </span> <asp: repeater ID = "repNews" runat = "server"> <ItemTemplate> <p class = "con_time"> Release Date: <% # DataBinder. eval (Container. dataItem, "createTime", "{0: yyyy-MM-dd HH: mm: ss }") %> <% -- here, createTime is the field name in dt -- %> </p> </ItemTemplate> </asp: Repeater>

In fact, most of the time formats in the system, those format conversion functions are still "recognized", but if some of them set their system time formats to "Saturday"Danny12:42:10 ", some are set to" Saturday"Hu Yuyang12:42:10 "......, These functions certainly cannot guess so many custom cases.

Therefore, in the process of designing the software, it is best to remove the client, to ensure the compatibility of various use environments, and to generate the time in the database, similarly, only the time in the database is displayed (to avoid filtering by the client ).

2. Change the Data Type of the stored time in the database to varchar (), however, it is best to make the time automatically generated in the database (an input without a format may also cause an output error), because the storage type is varchar (), therefore, the obtained value is considered as a string. In this case, when the time format is converted, the process of converting the obtained time to the value in the client time format is missing, convert the time strings in the database directly (in this case, those conversion functions can recognize the time functions in the database). The time format of the client does not affect the conversion process.

However, the type of the storage time in the database may also cause some trouble if it is stable:

The time in the database is only used for display and search, so the impact is not big. However, if you perform some algorithms on the time field, such as computing week, DateDiff, and DateAdd, it will be troublesome, in particular, the conversion type in large data queries will affect the efficiency.

Summary

In the database, should I use varchar or datetime to store the date field type? The two methods have their own advantages. datetime can use the computing functions in the SQL function library to increase the efficiency of time computing. varchar type can show its advantages in character encoding. If you do not need to perform a large amount of computing in the future, you can select the varchar type. Otherwise, select the datetime type.

How can I retrieve the values of datetime fields stored in the database table from the database and store them in a variable?

This can be achieved in the database,
1] For example, sql2000 or sql2005:

Declare @ name varchar (100)
Select @ name = sname from student where s_id = 100
Print @ name

2] in Oracle, you can write as follows:

Set serveroutput on;

Declare
A varchar2 (20 );
Begin
Select sname into a from student where s_id = 100;
Dbms_output.put_line ();
End;

In a database, a field type in a table is reserved. It is used to store the date. How can we compare the data within two days?

The following applies to oracle databases:

Select * from tablename
Where to_date (col_name, 'yyyymmdd') between: date1 and: date2

Tablename is your table name, col_name is the sequence type you use to store dates, and date1 and date2 are the two dates you want to compare

Remember, 'yyyymmdd' should be changed based on the data format stored in your table:
If the format of the field stored in the table is 20100914, it is written as 'yyyymmdd'
For example, the format of the field stored in the table is, it is written as 'yyyy-MM-DD'
For example, if the format of the field stored in the table is 12:30:20, it is written as 'yyyy/MM/DD HH24: MI: ss'
For example, the field format in the table is 14-SEP-2010, written as 'dd-MON-YYYY'
...