Shulou(Shulou.com)05/31 Report--

What is the DateTime storage format? in view of this question, this article introduces in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and feasible way.

What is the DateTime storage format

Before I explain the DateTime storage format, a concept needs to be corrected. The parts that make up the DateTime type: year,Month,day,hour,minute,second,millisecond, not stand-alone storage. Instead, DateTime as a whole is stored in two 4byte integers.

A single DateTime type requires 8byte storage, and the first four byte store date information before or after 1900-1-1. The last four byte stores the time information of the day, and the time in Datetime can be accurate to 1/3millisecond. The data range supported by the DateTime type is from January 1, 1753 to December 31, 9999. Why 1753? Earlier dates can be supported from a technical point of view. The restriction of 1753 was mainly the conversion from Julian to Greenwich mean time.

The SmallDateTime type uses the 4byte type, the first two byte stores the date from January 1, 1900 to the present, and the last two byte can be accurate to the minute storage time information. The data range represented by the SmallDateTime type is from 1900-1-1 to 2079-6-1.

How to use DateTime

The use of DateTime is a complicated thing. How to correctly represent the date? What happens when you enter something that cannot be accurately represented, such as "2006061123 laxity 59 lac 59? How to separate the date from the time? These issues will be discussed next.

1.Literals

Representing the input of a DateTime content in T-SQL is a very tricky thing. In an Insert or Update statement that requires a DateTime type, when you enter a string to represent the date content, the database system implicitly completes the conversion between data types. Of course, when there are multiple operands, the immediate priority of the operation depends on the data type of the Operand. The DateTime type takes precedence over the String type. When DateTime type data is compared to Stirng type data, String types are implicitly converted to DateTime types.

There are different transformations when representing DateTime-type data, which adds complexity to things. For example, "02Compact 12amp06" has different meanings for different people. When you convert this string to a DateTime type, SQLServer converts accordingly according to the language setting (langeagesettingsofsession) of the session. The conversation language depends on the default language of the logger. However, this setting can be modified through the SetLanguage option. We can also control how DateTime is converted by setting the SetDate-Format option. The SetLanguage option implicitly sets Date-Format to conform to language habits.

Example (from Microsoftmsdn):

Declare@todayvarchar (10) set@today='12/3/2007'setlanguageitalianselectdatename (month,@today) as'monthname'setlanguageus_englishselectdatename (month,@today) as'monthname'gooutput:monthname1.marzomonthname1.December

Although we can control the conversion of DateTime type data through the Set option, we need to note that the Set option modifies the language settings for the entire session. So what happens to the code in the session that relies on the default language settings after the Setlanguage operation? In the international application, the consideration of this aspect is very important.

Therefore, when writing code, I try to write code that does not depend on system settings and transformations. In SQLServer, DateTime has two formats that are independent of system settings. I prefer formats with no delimiters between date parts, such as "[yy] yymmdd[ hh: mi: [ss] [.mmm]]", specific data such as "20060312", "060312", "2006031223.59.59.999" and so on. The DateFormat and language settings do not affect strings in this format. If you need to display delimiters between years, months, etc., it is recommended to use the following format "yyyy-mm-ddThh:mi:dd [.mmm]", specific data such as "2006-03-12T14:23:05". (note: the format of "yyyy-mm-ddThh:mi:dd [.mmm]" still depends on the language setting after the test. I don't know if I did it wrong.

In addition to the Set option, we can also use the Convert method to display the conversion of DateTime data to different formats. Such as Convert (datetime,'12/02/2006',3). The result is "2006-02-1200".

This is the answer to the question about what the DateTime storage format is. I hope the above content can be of some help to you. If you still have a lot of doubts to solve, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.