Recently, I used reports in a power system. I often need to operate SQL dates. Write down some common SQL date operation functions
/**/ /**/ /**/ /* Use of datepart () Functions
* The datepart () function allows you to conveniently retrieve all parts of the period.
* Date: 18:15:36. 513
* YY: 2006 of the year
* Mm: 7 months
* DD: 2 days in the month
* DY: 183 days in the year
* WK: week 27 in the year
* DW: The day in the week.
* QQ: Quarter 3 of the year
* Hh: 18 hours
* Mi: 15 minutes
* Ss: 36 seconds
* The following simple statements demonstrate the obtained results:
*/
Select Getdate ()
Select Datepart (Mm, Getdate ())
Select Datepart (Yy, Getdate ())
Select Datepart (DD, Getdate ())
Select Datepart (Dy, Getdate ())
Select Datepart (Wk, Getdate ())
Select Datepart (DW, Getdate ())
Select Datepart (Qq, Getdate ())
Select Datepart (Hh, Getdate ())
Select Datepart (MI, Getdate ())
Select Datepart (Ss, Getdate ())
Select Datediff (DD, Getdate (), ' 12/25/2006 ' ) -- Calculate the number of days from today to 12/25/2006
Select Datediff (Mm, Getdate (), ' 12/25/2006 ' ) -- Calculate the number of months from today to 12/25/2006
Select Datename (Mm, Getdate ()) -- Get the month of today in the form of a string instead of a number (07)
Select Dateadd (DD, 30 , Getdate ()) -- Add 30 days to the current date
Select Dateadd (Mm, 3 , Getdate ()) -- Add 3 months to the current date
Select Dateadd (Yy, 1 , Getdate ()) -- Add one year to the current date
Select Dateadd (Qq, Datediff (Qq, 0 , Getdate ()), 0 ) -- Get the first day of this quarter
Select Dateadd (Mm, - 3 , Dateadd (Qq, Datediff (Qq, 0 , Getdate ()), 0 )) -- Get the first day of the primary and quarterly -- In addition, the date returned by using the getdate () function is in milliseconds. Sometimes, we only need to retrieve part of the data, such.
Select Convert ( Varchar ( 10 ), Getdate (), 120 ) -- Result:
/**/ /*
varchar (10) indicates that the truncation length is 10 characters.
getdate () indicates the date to be processed, you can also directly give the date string
For example: 10:00:28. 420
120: getdate () for more information about the format, see
parameter results
100 05 8 2006 PM
101 05/08/2006
102 2006.05.08
103 08/05/2006
104 08.05.2006
105 08-05-2006
106 08 05 2006
107 05 08,200 6
108 21:30:51
109 05 8 2006 9:31:11
110
> 111 2006/05/08
112 20060508
113 08 05 2006 21:31:59
114 21: 33: 06: 503
120 21:33:38
*/
