Date and Time Functions

Date and Time type:

Represents the date and time data type of the date and the time within the day

The database can recognize the time and date in the following format.

1. Letter date format

2. digit Date Format

3. unseparated strings

Example:

Declare @ date datetime --Set@ Date = '0000l 15,199 8 '--Set@ Date = '2014/1/123'Set@ Date = '000000' select @ date

Result:

1900-12-11 00:00:00. 000

Getdate:

Returns the current system time and date.

Syntax: getdate ()

Example: Select getdate ()

Result: 11:14:36. 483

Getutcdate:

Returns the current GMT Standard Time.

The current UTC time is set from the current local time and the time zone of the computer operating system.

Syntax: getutcdate ()

Example: Select getdate (), getutcdate ()

Result:

Year

Returns the year from the specified date.

Syntax: Year (date)

Month

Returns the month from the specified date.

Syntax: Month (date)

Day

Returns the day from the specified date.

Syntax: Day (date)

Example: Select Year (getdate (), month (getdate (), Day (getdate ())

Result:

Datepart:

Returns the integer of the specified part of the specified date.

Syntax: datepart (part, date)

Return type: int

Parameter: Part

Date	Abbreviations
Year	YY, yyyy
Quarter	QQ, Q
Month	Mm, m
Dayofyear	Dy, y
Day	DD, d
Week	Wk, WW
Weekday	DW
Hour	HH
Minute	Mi, n
Second	SS, S
Millisecond	MS

Declare @ time datetimeSet@ Time = getdate ()

Example 1:

Select @ time, datepart (year, @ time), datepart (month, @ time), datepart (day, @ time), datepart (hour, @ time), datepart (minute, @ time), datepart (second, @ time), datepart (millisecond, @ time)

Result:

Example 2:

Select @ time, datepart (quarter, @ time), datepart (dayofyear, @ time), datepart (Week, @ time), datepart (weekday, @ time)

Result:

Datename

Returns the string of the specified part of the specified date.

Syntax: datename (part, date)

Return type: nvarchar

Parameter: similar to datepart.

Example 1:

Select @ time, datename (year, @ time), datename (month, @ time), datename (day, @ time), datename (hour, @ time), datename (minute, @ time), datename (second, @ time), datename (millisecond, @ time)

Result:

Example 2:

Select @ time, datename (quarter, @ time), datename (dayofyear, @ time), datename (Week, @ time), datename (weekday, @ time)

Result:

 

Note:

Difference between datepart and datename:

Different return types. datepart returns the int type, while datename returns the nvarchar type.

Dateadd:

Add a period of time to the specified date, and then return a new value.

Syntax: dateadd (part, number, date)

Parameters:

Part: Same as above

Number: the value to be added. Note: if it is not an integer, the fractional part will be discarded without rounding.

Date: Date.

Example:

Select getdate (), dateadd (month, 12, getdate (), dateadd (minute, 60, getdate ())

Result:

Datediff:

Returns the difference between the specified date and the specified date. The first date is used to subtract the second date.

Syntax: datediff (part, startdate, enddate)

Example:

Declare @ startdate datetimeSet@ Startdate = getdate () Declare @ enddate1 datetimeSet@ Enddate1 = dateadd (year, 1, getdate () Declare @ enddate2 datetimeSet@ Enddate2 = dateadd (day, 8, getdate () Select @ startdate start time, @ enddate1 end time 1, @ enddate2 end time 2, datediff (year, @ startdate, @ enddate1) year difference, datediff (day, @ startdate, @ enddate2) day difference, datediff (Week, @ startdate, @ enddate2) Week difference

Result: