T-SQL Programming 1: DateTime Datatype part1

DateTime is one of the most commonly used and problematic data types. The following problems exist:

1. DateTime Storage Format

Before explaining the DateTime storage format, you must correct a concept. Components of the DateTime type: year, Month, day, hour, minute, second, millisecond, not independent storage. On the contrary, DateTime is stored as a whole with two 4byte integers.

An independent DateTime type requires 8 bytes of storage, and the first four bytes store the date information before or after-1. The last four bytes store the time information of the day. The time in Datetime can be accurate to 1/3 millisecond. The supported data range of the DateTime type is: January 1, January 1-9, 1753, December 31, 999. Why is it 1753? The earlier date is supported technically. The limitation of 1753 was mainly the transition from Julian to Greenwich Mean Time.

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

Ii. How to Use DateTime

The use of DateTime is complicated. How can we correctly represent a date? What happens when the input is "20060611 23: 59: 59: 59: 999" and cannot be accurately expressed? How can we separate a date from a time? We will discuss these issues.

1. Literals

Representing the input of a DateTime content in the T-SQL is a very tricky that doesn't know how to translate. In an Insert or Update statement that requires the DateTime type, when a string is input to represent the date content, the database system implicitly converts the data types. Of course, when there are multiple operands, the priority of the operation depends on the Data Type of the operand. The priority of the DateTime type is higher than that of the String type. When the DateTime type data is compared with the Stirng type data, the String type is implicitly converted to the DateTime type.

There are different conversions when representing DateTime type data, which increases the complexity of things. For example, "02/12/06" has different meanings for different people. When this string is converted to the DateTime type, SQL Server sets langeage settings of session BASED ON THE session language) to convert accordingly. The session language depends on the default language of the login account. However, this setting can be modified using the Set Language option. You can also control how to convert DateTime by setting the Set Date-Format option. The Set Language option implicitly sets Date-Format to conform to Language conventions.

For example, Microsoft msdn ):

 
 

  
  
Declare @today varchar(10)  
  
  
set @today='12/3/2007' 
  
  
set language italian  
  
  
select datename(month,@today) as 'month name' 
  
  
set language us_english  
  
  
select datename(month,@today) as 'month name' 
  
  
go  
  
  
output :   
  
  
  month name              
  
  
1.  marzo  
  
  
  month name 
  
  
1.  December  
  
  
 
 
 

Although we can use the Set Option to control the conversion of DateTime type data, we need to note that the Set option will modify the language settings of the entire session. So, the session depends on the Code Set by default. What will happen after the Set language operation? In international applications, this consideration is very important.

Therefore, when writing code, I try to write code that does not depend on system settings and conversion. In SQL Server, DateTime has two formats, independent of system settings. I prefer a format that does not have a separator between each date, for example, "[yy] yymmdd [hh: mi: [ss] [. mmm] ", specific data such as" 20060312 "," 060312 "," 20060312 23.59.59.999 ", and so on. The DateFormat and language settings do not affect strings in this format. If you want to display separators between the year and month, we recommend that you use the format yyyy-mm-ddT hh: mi: dd [. mmm] ", for example," 14:23:05 ". Note: The "yyyy-mm-ddT hh: mi: dd [. mmm]" format is tested and the dependency language settings are still tested. I don't know if it is my fault. Too many)

In addition to the Set option, we can also use the Convert method to display the converted DateTime data in different formats. For example, Convertdatetime, '2014/1/123', 3). The returned result is "2006-02-12 00:00:00. 000 ".