SQL Server date details

For developers, date processing may be simple and difficult. Based on your past development experience and online examples, we can summarize some date-related operations for your backup and sharing.

For developers, date processing may be simple and difficult. Based on your past development experience and online examples, we can summarize some date-related operations for your backup and sharing.

I. date type:

For SQL Server 2008 (2000 or even 2005 have been slightly eliminated, so I will not elaborate much here, and I am working on 2008R2. So it is not guaranteed to be usable before 08), date types include:

Note: The date and time values of all systems must be the OS of the computer on which the SQL Server instance runs.

Each date type has its own scope of use. Of course, it is the best choice to apply it. Remember not to use datetime for convenience. It has a great impact on performance. For example:

A table with 0.1 billion rows of data and 10 columns of date type (this is completely possible for some historical tables ). If datetime is used, the storage space of the Light part is: 10*100000000*8 Bytes/(1024*1024) ≈ 7629M ≈ 7.4G. Of course, if such a need exists, it should be used again. If the business does not need to be so precise (because datetime is accurate to 0.00333 seconds), it only needs to be accurate to 1 minute, so we do not hesitate to use smalldatetime, this reduces the space by half, that is, about 3.7 GB. There are many advantages to reduce space, such as reducing the size of backup and database files, and allowing a limited budget to do more. In addition, the data page is fixed to 8 KB, and the smaller the size of a single page, the more data that can be stored, and the fewer pages to be accessed during query, alleviating the I/O pressure. At the same time, the use of indexes is more effective.

Therefore, the importance of "design" can be reflected here.

Ii. Date functions: Date functions are the basis for processing dates. Keeping in mind that date functions can reduce a lot of programming work.

High-precision system Date and Time Functions

The accuracy depends on the computer hardware and Windows version used to run the SQL Server instance. Note: 2012 is effective, and only 2012 is available.

Low-precision system Date and Time Functions

Function used to obtain the date and time

A function used to obtain the date and time value from a part.

Function used to obtain the date and time difference

Function used to modify the date and time values

Function used to set or obtain the session format

Function used to verify the date and time values

Iii. Detailed date operations:

3.1. Note: SQL Server interprets 0 as January 1, January 1, 1900.

3.2 you can use the SET keyword to change the date settings for some current sessions:

Set datefirst {number | @ number_var }:

Set the first day of a week to a number ranging from 1 to 7. 7 is the default first day, that is, Sunday. To view the current settings of set datefirst, use the @ DATEFIRST function. Note that this value is executed at runtime

Set dateformat {format | @ format_var }:

Sets the order for interpreting the month, day, and year dates of the date, smalldatetime, datetime, datetime2, and datetimeoffset strings. Valid parameters include mdy, dmy, ymd, ydm, myd, and dym. The default values are mdy. SETDATEFORMAT overwrites the implicit date format setting of SETLANGUAGE.

Set the session language: although the language is used here, the format of the date will be affected:

Set language Italian;

GO

SELECT @ DATEFIRST;

GO

Set language us_english;

GO

SELECT @ DATEFIRST;

3.3 common function operations:

4. Common date processing case: This is the focus of the article, because most of the content above can be found in online books

For a given date, the related values are calculated. At present, I have encountered many such problems in my work. For some special calendars, the date is not met, so I did not summarize it:
The Code is as follows:
-- Define a given day
DECLARE @ Date DATETIME = GETDATE ();

SELECT @ Date AS 'current time'
, DATEADD (DD,-1, @ Date) AS 'previous Day'
, DATEADD (DD, 1, @ Date) AS 'Next Day'
/* Monthly computing */
, DATEADD (MONTH, DATEDIFF (MONTH, 0, @ Date), 0) AS 'E' -- in SQL Server, 0 represents. Through monthly operations, the daily duration is always 1.
, DATEADD (DD,-1, DATEADD (MONTH, 1 + DATEDIFF (MONTH, 0, @ Date), 0) AS 'end of the MONTH (precise to the day) '-- find and deduct one day from the beginning of next month. We recommend that you use DATEADD instead of "-1"
, DATEADD (SS,-1, DATEADD (MONTH, 1 + DATEDIFF (MONTH, 0, @ Date), 0) AS 'END of the month' (precise to the decimal place of datetime )'
, DATEADD (MONTH, DATEDIFF (MONTH, 0, @ Date)-) AS 'Day of last MONTH'
, DATEADD (DAY,-1, DATEADD (DAY, 1-DATEPART (DAY, @ Date), @ Date) AS 'last DAY of last month'
, DATEADD (MONTH, DATEDIFF (MONTH, 0, @ Date) +) AS 'Day of next month'
, DATEADD (DAY,-1, DATEADD (MONTH, 2, DATEADD (DAY, 1-DATEPART (DAY, @ Date), @ Date) AS 'last DAY of next month'
/* Weekly calculation */
, DATEADD (WEEKDAY, 1-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of the Week (Sunday) '-- note that this is related to the value of @ datefirst.
, DATEADD (WEEK, DATEDIFF (WEEK,-1, @ Date),-1) AS 'sunday' of the WEEK -- note that this is related to the value of @ datefirst.
, DATEADD (DAY, 2-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of the week '-- note that this is related to the value of @ datefirst, other days, and so on.
, DATEADD (WEEK,-1, DATEADD (DAY, 1-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of the last WEEK (Sunday) '-- note that this parameter is related to the value of @ datefirst.
, DATEADD (WEEK, 1, DATEADD (DAY, 1-DATEPART (WEEKDAY, @ Date), @ Date) AS 'Day of next WEEK (Sunday) '-- note that this parameter is related to the value of @ datefirst.
, DATENAME (WEEKDAY, @ Date) AS 'the day of the week'
, DATEPART (WEEKDAY, @ Date) AS 'the day of the week' -- Return Value: 1-Sunday, 2-Monday, 3-Tuesday ...... 7-Saturday
/* Annual calculation */
, DATEADD (YEAR, DATEDIFF (YEAR, 0, @ Date), 0) AS 'Year'
, DATEADD (YEAR, DATEDIFF (YEAR,-1, @ Date),-1) AS 'yearend'
, DATEADD (YEAR, DATEDIFF (YEAR,-0, @ Date)-1, 0) AS 'beginning of last year'
, DATEADD (YEAR, DATEDIFF (YEAR,-0, @ Date),-1) AS 'end of last year'
, DATEADD (YEAR, 1 + DATEDIFF (YEAR, 0, @ Date), 0) AS 'early next year'
, DATEADD (YEAR, 1 + DATEDIFF (YEAR,-1, @ Date),-1) AS 'end of next year'
/* Quarterly calculation */
, DATEADD (QUARTER, DATEDIFF (QUARTER, 0, @ Date), 0) AS 'beginning of the QUARTER'
, DATEADD (QUARTER, 1 + DATEDIFF (QUARTER, 0, @ Date),-1) AS 'end of season'
, DATEADD (QUARTER, DATEDIFF (QUARTER, 0, @ Date)-) AS 'first QUARTER of last season'
, DATEADD (QUARTER, DATEDIFF (QUARTER, 0, @ Date),-1) AS 'quarter-end-of-season'
, DATEADD (QUARTER, 1 + DATEDIFF (QUARTER, 0, @ Date), 0) AS 'start of next season'
, DATEADD (QUARTER, 2 + DATEDIFF (QUARTER, 0, @ Date),-1) AS 'end of next season'


5. Suggestions:

Create time dimension table: In my previous work, you often need to query the time range (precise to the day). At this time, you can create a table with each row corresponding to one day, the other columns are the required date, such as the end of the first quarter, the end of the month, the end of the year, or even the next year. For direct calls, and even if the data is stored for 10 years, there will be more than 3000 records. If you have such requirements, consider using them.

Vi. Quick query manual: the date is often converted to the simplified type before processing. Therefore, part of the Conversion Result is posted here.

The Code is as follows:
Select CONVERT (varchar (100), GETDATE (), 0) -- 05 16 2006 AM
Select CONVERT (varchar (100), GETDATE (), 1) -- 05/16/06
Select CONVERT (varchar (100), GETDATE (), 2) -- 06.05.16
Select CONVERT (varchar (100), GETDATE (), 3) -- 16/05/06
Select CONVERT (varchar (100), GETDATE (), 4) -- 16.05.06
Select CONVERT (varchar (100), GETDATE (), 5) -- 16-05-06
Select CONVERT (varchar (100), GETDATE (), 6) -- 16 05 06
Select CONVERT (varchar (100), GETDATE (), 7) -- 05 16, 06
Select CONVERT (varchar (100), GETDATE (), 8) -- 10:57:46
Select CONVERT (varchar (100), GETDATE (), 9) -- 05 16 200610: 57: 46: 827AM
Select CONVERT (varchar (100), GETDATE (), 10) -- 05-16-06
Select CONVERT (varchar (100), GETDATE (), 11) -- 06/05/16
Select CONVERT (varchar (100), GETDATE (), 12) -- 060516
Select CONVERT (varchar (100), GETDATE (), 13) -- 16 05 2006 10: 57: 46: 937
Select CONVERT (varchar (100), GETDATE (), 14) -- 10: 57: 46: 967
Select CONVERT (varchar (100), GETDATE (), 20) -- 10:57:47
Select CONVERT (varchar (100), GETDATE (), 21) -- 10:57:47. 157
Select CONVERT (varchar (100), GETDATE (), 22) -- 05/16/06 10:57:47 AM
Select CONVERT (varchar (100), GETDATE (), 23) --
Select CONVERT (varchar (100), GETDATE (), 24) -- 10:57:47
Select CONVERT (varchar (100), GETDATE (), 25) -- 10:57:47. 250
Select CONVERT (varchar (100), GETDATE (), 100) -- 05 16 2006 AM
Select CONVERT (varchar (100), GETDATE (), 101) -- 05/16/2006
Select CONVERT (varchar (100), GETDATE (), 102) -- 2006.05.16
Select CONVERT (varchar (100), GETDATE (), 103) -- 16/05/2006
Select CONVERT (varchar (100), GETDATE (), 104) -- 16.05.2006
Select CONVERT (varchar (100), GETDATE (), 105) -- 16-05-2006
Select CONVERT (varchar (100), GETDATE (), 106) -- 16 05 2006
Select CONVERT (varchar (100), GETDATE (), 107) -- 05 16,200 6
Select CONVERT (varchar (100), GETDATE (), 108) -- 10:57:49
Select CONVERT (varchar (100), GETDATE (), 109) -- 05 16 200610: 57: 49: 437AM
Select CONVERT (varchar (100), GETDATE (), 110) -- 05-16-2006
Select CONVERT (varchar (100), GETDATE (), 111) -- 2006/05/16
Select CONVERT (varchar (100), GETDATE (), 112) -- 20060516
Select CONVERT (varchar (100), GETDATE (), 113) -- 16 05 2006 10: 57: 49: 513
Select CONVERT (varchar (100), GETDATE (), 114) -- 10: 57: 49: 547
Select CONVERT (varchar (100), GETDATE (), 120) -- 10:57:49
Select CONVERT (varchar (100), GETDATE (), 121) -- 10:57:49. 700
Select CONVERT (varchar (100), GETDATE (), 126) -- 2006-05-16T10: 57: 49.827
Select CONVERT (varchar (100), GETDATE (), 130) -- 18 ???? ?????? 142710: 57: 49: 907AM
Select CONVERT (varchar (100), GETDATE (), 131) -- 18/04/142710: 57: 49: 920AM