Database skills-clever use of SQL Server date functions

 

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 you to spare and share with you:
 
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.

Common date processing case: This is the focus of the article, because most of the above content 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:
-- 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'

Suggestion:
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.
 
Quick query manual: the date is often converted to the simplified type before processing. Therefore, part of the Conversion Result is pasted here.
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

Link: http://topic.csdn.net/u/20120613/01/ac7d8d4f-1aa6-4bf5-aeda-d31eccf33fcb.html