cool hit counter Should the field type for storing dates in a database be varchar or datetime?_Intefrankly

Should the field type for storing dates in a database be varchar or datetime?

Copyright: This is an original post by the blogger and may not be reproduced without the blogger's permission.


Some time ago I saw an article in Baidu Experience "How to display your (loved one's) name in the bottom right corner of your computer , I had heard of this tip before, but had never actually set it up, so I put it into practice out of curiosity.

The finished setup looks like this, with my name "Danny" added to the time area in the bottom right corner :)

The above is background. I didn't expect this little trick to get me in trouble (and certainly an opportunity to learn and improve).

The string is not recognized as a pseudo-valid DateTime

The news distribution system being done has a database that stores time in fields of type datetime and the field values are all automatically fetched on the server side. When trying to display the time in the format "yyyy-MM-dd HH:mm:ss" on the client, a problem occurs: "The string is not recognized as a pseudo-valid DateTime".

The error page is as follows.

Get the exception, the exception message reads.

The error key code is.

 lblCreateTime.Text = Convert.ToDateTime(news. CreateTime). ToString();   // [Note]: lblCreate is a Lable for the front-end display page; news is the "News" entity class obtained after the query, and CreateTime is a field of it.

Guessing it's a problem with my local computer's time format, got a little time news on the client. The value of CreateTime in the format "2014/8/23 Saturday Danny 13:10:14" and the time of the record is stored in the database as "2014-08-2313:10:14". After testing, if news. This error is not generated when CreateTime is stored in the database as type varchar(). So it is known that the process of time format conversion here is as follows.

In this process, the system determines that the value obtained from the database is datetime type, so the value obtained (for example, here the time value obtained from the database is "2014-08-2313:10:14") is converted to the local time format (for example, my computer's time format "2014/8/23 Saturday Danny13:10:14"), and in the last step of format conversion, the system is unable to recognize the user-defined time format (for example, here "2014/8/23 Saturday Danny13:10:14"), thus reporting an error.

Two articles summarizing time format conversions in were found online at formatting time and date Several methods of time formatting in。So many ways., Broadly speaking, I've broken it down into two ways: In the interface code(*.aspx) upconversion & In the backend code(*.aspx.cs) upconversion。


Two approaches were used to address this issue.

1. If the data type for storing time in the database is datetime, then avoid converting the time format in the backend code (*.aspx.cs) and put the task of format conversion on the interface code (*.aspx).

For example, in the above example, regardless of the format of the time obtained, do not perform any operations on the value of this time in the backend (such as assigning a value, etc., otherwise the system will implicitly convert the time), but directly format it in the interface code (*.aspx) using methods such as DataBinder, Eval, etc.

Backend key code.

    <span style="white-space:pre">	</span>DataTable dt = new NewsManager().SelectById(newsid);     // Here's what we gotdt for data retrieved from the direct database
    <span style="white-space:pre">	</span>    repNews.DataSource =dt;

Frontend Key Code.

<span style="white-space:pre">	</span><asp:Repeater ID="repNews" runat="server">
                <p class="con_time">
                     Posted on: 
                    <%# DataBinder.Eval(Container.DataItem,"createTime","{0:yyyy-MM-dd HH:mm:ss}") %>       <%-- this placecreateTime for the abovedt The name of the field in--%>

In fact, most of the system time format, those format conversion functions still "know", but if some of them set their system time format to "2014/8/23 SaturdayDanny 13:10:14", with some set to "Sat 2014/8/23 Hu Yuyang 13:10:14" ...... These functions certainly can't guess so many mid-custom cases.

Therefore, in the process of designing the software, it is best to shave off the client as a factor to ensure the compatibility of various usage environments, the time is generated in the database, and likewise the display shows only the time in the database (to avoid client-side filtering).

2, the database to store the time of the data type to varchar (), but this time it is best to let these times are automatically generated in the database (an input without format may also lead to output errors), because the storage type is varchar (), so the value obtained is also considered a string, this time in the conversion of the time format is less in the above figure [the time obtained into the value of the client time format] steps, directly the time string in the database for conversion (this time those conversion functions are able to identify the time function in the database), the client time format no longer affect the conversion process.

However, the type of time stored in the database can also cause some problems if it is of character type.

The time in the database is only used to display, find, then the impact is not too big, but if the time field for some algorithms such as calculating the day of the week, DateDiff, DateAdd, etc., then it is a problem, especially in large data queries to convert the type is to affect the efficiency


Should the field type for storing dates in the database be varchar or datetime ? These two methods have their own advantages, datetime can be borrowed from the sql library of arithmetic functions, increasing the efficiency of time on a variety of operations; while the varchar type can show the advantages of the character encoding. If the time stored does not require extensive calculations in the future, consider choosing the varchar type, or conversely, the datetime type.

1、AI infiltrates the travel industry robots will plan your next vacation
2、2018 Technology Development and Media Innovation Outlook
3、Mondaynbsp 21st April
4、Information Group Big Data Operation Ltd was officially inaugurated
5、Beijing Haidian partners with Baidu to build city brain

    已推荐到看一看 和朋友分享想法
    最多200字,当前共 发送