SQL --> date functions

Execution instance (Table: life_unite_product has the createtime time field)
Select day (createtime) from life_unite_product -- obtains the day value of the time field.
Select month (createtime) from life_unite_product -- Take the month value of the time field
Select year (createtime) from life_unite_product -- get the year Value of the time field
Select datepart (yy, createtime) from life_unite_product -- Obtain the annual value of the time field
Select datepart (qq, createtime) from life_unite_product -- Obtain the quarter value of the time field
Select datepart (mm, createtime) from life_unite_product -- Obtain the monthly value of the time field
Select datepart (dy, createtime) from life_unite_product -- Obtain the day of the year from the time field
Select datepart (dd, createtime) from life_unite_product -- obtains the day value of the time field.
Select datepart (wk, createtime) from life_unite_product -- Obtain the week of the year from the time field
Select datepart (dw, createtime) from life_unite_product -- obtain the number of workdays of the week in that year from the date field (the workdays are calculated from Sunday)
Select datepart (hh, createtime) from life_unite_product -- obtains the hour value of the time field.
Select datepart (mi, createtime) from life_unite_product -- Obtain the minute value of the time field
Select datepart (ss, createtime) from life_unite_product -- obtains the second value of the time field.
Select datepart (MS, createtime) from life_unite_product -- get the millisecond value of the time field
Select dateadd (yy,-1, createtime) from life_unite_product ---- obtain the time field (the year is reduced by 1)
Select dateadd (mm, 3, createtime) from life_unite_product ---- obtain the time field (3 is added to the month)
Select dateadd (dd, 1, createtime) from life_unite_product ---- obtain the time field (1 is added on the Day)
Select DATEDIFF (yy, createtime, getdate () from life_unite_product -- the year difference from the current date
Select DATEDIFF (mm, createtime, getdate () from life_unite_product -- the month difference from the current date
Select DATEDIFF (dd, createtime, getdate () from life_unite_product -- the day difference from the current date
Select DATEDIFF (mi, createtime, getdate () from life_unite_product -- the difference in minutes from the current date
Select datename (yy, createtime) from life_unite_product -- get the Year Value of the time field
Select datename (mm, createtime) from life_unite_product -- Obtain the monthly value of the time field
Select datename (dd, createtime) from life_unite_product -- obtains the day value of the time field.
Select getdate () -- get the current time
Date Functions
1. day (date_expression)
Returns the date value in date_expression.
2. month (date_expression)
Returns the month value in date_expression.
3. year (date_expression)
Returns the Year Value in date_expression.
4. DATEADD ()
DATEADD (,,)
Returns the new date generated by the specified date plus the specified additional date interval number. The value of "datepart" is as follows:
5. DATEDIFF ()
DATEDIFF (,,)
Returns the difference between two specified dates in datepart, that is, the difference between date2 and date1. The result value is an integer with positive and negative signs.
6. DATENAME ()
DATENAME (,)
Returns the specified part of the date in the form of a string. Specified by datepart.
7. DATEPART ()
DATEPART (datepart, date)
Returns the specified part of the date in the form of an integer. This part is specified by datepart.
DATEPART (dd, date) is equivalent to DAY (date)
DATEPART (mm, date) is equivalent to MONTH (date)
DATEPART (yy, date) is equivalent to YEAR (date)
The following table lists the datepart options and abbreviations recognized by SQL Server Compact Edition:
Abbreviation of date
Year yy, yyyy
Quarterly qq and q
Month mm, m
Dy, y
Date dd, d
Week wk, ww
Workday * dw
Hour hh
Min mi, n
Ss, s
Millisecond MS
8. GETDATE ()
Returns the current date and time of the system in the default format of DATETIME.