SQL Server Date operation

SQL Server Date operation

Select GETDATE () as ' current date ',
Datename (Year,getdate ()) as ' year ',
Datename (Month,getdate ()) as ' month ',
Datename (Day,getdate ()) as ' Day ',
Datename (Dw,getdate ()) as ' Week ',
Datename (Week,getdate ()) as ' Week number ',
Datename (Hour,getdate ()) as ' when ',
Datename (Minute,getdate ()) as ' min ',
Datename (Second,getdate ()) as ' s '

Results:
2009-08-13 23:07:15.403 2009 08 13 Week 433 23 7 15

function parameters/Functions
GetDate () returns the current date and time of the system
DateDiff (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates in the interval specified date2-date1
DATEADD (interval,number,date) with the date specified in interval, plus number
DatePart (interval,date) returns the integer value corresponding to the specified portion of the date, interval
Datename (interval,date) returns the name of the string that corresponds to the specified part of the date, interval


The setting values for the parameter interval are as follows:
value abbreviation (SQL Server) description
Year Yy 1753 ~ 9999
Quarter Qq Season 1 ~ 4
Month Mm 1 ~ 12
Day of the year Dy the number of days in the year, the day of the first 1-366
Day Dd, 1-31
Weekday Dw Number of days of the week, day of the week 1-7
Week Wk Week, week of the year 0 ~ 51
Hour Hh 0 ~ 23
Minute Mi minutes 0 ~ 59
Second Ss sec 0 ~ 59
Millisecond MS Ms 0 ~ 999

Example:
1.GetDate () for SQL Server:select GetDate ()

2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 seconds
DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days

3.DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 that is Monday (Sunday is 1, Saturday is 7)
DatePart (' d ', ' 2005-7-25 22:56:32 ') returns a value of 25, or 25th
DatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 206 that is the No. 206 Day of the Year
DatePart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 that is 2005

SQL Server Date operation