Should the field type of the date stored in the database be either varchar or datetime?

background:

a period of time in the Baidu experience saw an article "How to display your (beloved) name in the lower right corner of the computer", before also heard this little trick, but did not really set up, so out of curiosity to practice a bit.

After the set-up effect is as follows, the time area in the lower right corner adds my name "Danny":

The above is the background. I did not expect this little trick to bring Me trouble (and of course, a chance to learn and improve).

The string is not recognized as a pseudo-valid datetime

In the press release system that is being made, the field type of time stored in the database is a datetime type, and the field values are automatically obtained on the server side. When you want the client to display time in a "yyyy-mm-dd HH:mm:ss" format, there is a problem: "The string is not recognized as a pseudo-valid datetime":

Error pages such as:

the key code for the error is:     

Lblcreatetime.text = Convert.todatetime (news. Createtime). ToString ();  "Note": lblcreate displays a page for the front end a lable;news is the "News" entity class that was obtained after the query, createtime a field for it

Guess is my Computer time format problem, the client to get a moment news. The value of Createtime, in the format: "2014/8/23 Saturday, Danny 12:42:10", and the time that the record was stored in the database is "2014-08-23 12:42:10". After testing, if news. Createtime the type that is stored in the database is varchar (), this error is not generated. so you know, here's the time format conversion process:

In this process, the system determines that the value obtained from the database is a datetime type, so to convert the obtained value (for example, the time value obtained from the database to "2014-08-23 12:42:10") to the local time format ( such as the time format of my Computer) 2014/8/23 Saturday, Danny 12:42:10 "), in the final step of format conversion, the system does not recognize the user-defined time format (for example, here,"2014/8/23 Saturday, Danny 12:42:10 " ), resulting in an error.

i found two articles on the internet summarizing the conversion of time format in asp:several ways to format time anddate in ASP. so many ways, in general I divide it into two ways: on the Interface Code (*.aspx) on the conversion & in the background code (*.aspx.cs) conversion.

Workaround

There are two ways to solve this problem:

1, if the database storage time data type is datetime, then avoid in the background code (*.aspx.cs) conversion time format, the conversion of the format of the task to the Interface Code (*.aspx) ;

In the example above, no matter what the format of the acquisition time, in the background do not take any action on the value of this time (such as assignment, or the system will be the time implicit conversion), but directly in the Interface Code (*.aspx) with DataBinder, Eval and other methods to directly format :

background Key code:     

    <span style= "White-space:pre" ></span>datatable dt = new Newsmanager (). Selectbyid (NEWSID);     The resulting DT is the data from the direct database    <span style= "White-space:pre" ></span>    repnews.datasource =dt;            Repnews.databind ();

Front desk Key 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 for field names in the above DT--%>                </p>            </ Itemtemplate>        </asp:Repeater>

In fact, most of the time format in the system, those format conversion function or "know", but if some of their own system time format set to "2014/8/23 Saturday Danny 12:42:10", some set to "2014/8/23 Saturday Hu Yuyang 12:42:10 "..., these functions are sure not to guess that much in the custom case.

Therefore, in the process of design software, it is best to shaving the client this factor outside, to ensure the compatibility of various uses of the environment, time in the database, and also display only the time in the database (to avoid client filtering).

2. Change the data type of the storage time in the database to varchar (), but it is best to make these times automatically generated in the database (an unformatted input may also result in an output error) because the storage type is varchar (), So the obtained value is also considered to be a string, when the conversion time format is less in the "convert the acquired time to the value of the client time format" step, the database directly convert the time string (then those conversion functions can be recognized in the database time function), The client's time format no longer affects the conversion process.

However, the type of storage time in a database can be problematic if it is a character type:

The time in the database is only used to display, find, then the impact is not big, but if the time field to do some algorithms such as computing weeks, DateDiff, DateAdd, and so on, it is troublesome, especially the large data query conversion type is the impact of efficiency

Summary

Should the field type of the date stored in the database be either varchar or datetime? Both methods have advantages, and datetime can borrow the arithmetic functions in the SQL function library to increase the efficiency of time on various operations, while the varchar type can show advantages in character coding. You might consider choosing a varchar type if the stored time does not require a large amount of computation in the future, and conversely, choosing a datetime type.

Should the field type of the date stored in the database be either varchar or datetime?