MS SQL Server Time function

Date and time data types

Data type	Storage (bytes)	Date Range	Accuracy	Format example
Datetime	8	January 1, 1753-December 31, 9999	3 1/3 ms	YYYY-MM-DD hh:mm:ss.nnn
smalldatetime	4	January 1, 1900-June 6, 2079	1 minutes	YYYY-MM-DD hh:mm
Date	3	January 1, 01-9999 months December 31	1 days	Yyyy-mm-dd
Time	3-5	N/A	100 nanoseconds	hh:mm:ss.nnnnnnn
DateTime2	6-8	January 1, 01-December 31, 9999	100 nanoseconds	YYYY-MM-DD hh:mm:ss.nnnnnnn
Datetimeoffset	8-10	January 1, 01--December 31, 9999	100 nanoseconds	YYYY-MM-DD hh:mm:ss.nnnnnnn

---1, time and date functions except the Current_timestamp function pail the top parenthesis "()"; all the other functions need to make parentheses

Select GETDATE ()--return to current time
Select current_timestamp--is equivalent to GETDATE (), but follows the ANSI SQL
Select getUTCDate ()--returns the UTC date and time (Coordinated Universal Time)
Select Sysdatetime ()--current date and time
Select Sysutcdatetime ()--current UTC date and time (Coordinated Universal Time)
Select Sysdatetimeoffset ()--current datetime with time zone offset

--2, cast, convert function, and its try_ corresponding function Try_ function at the beginning of SQL SEVER2012 support
--Cast, convert function is used to convert the input value to the target type, if the conversion successfully output the value of the conversion, otherwise error;
--Try_cast, try_convert function; the function of two try_ functions is the same as its corresponding function, and the difference is that if the input value cannot be converted to the specified type, it returns NULL.

Select CAST (' 20160628 ' as DATE)
Select Try_cast (' 20160628 ' as DATE)

Select CAST (' 123456 ' as DATE)
Select Try_cast (' 123456 ' as DATE)

Select CONVERT (char), GETDATE (), 120)
Select Try_convert (char), GETDATE (), 120)

Select CONVERT (int, ' 123ACB ')
Select Try_convert (int, ' 123ACB ')

---3. The Switchoffset function adjusts the input DateTimeOffset time to the time of the specified time zone
Select Switchoffset (Sysdatetimeoffset (), ' -05:00 ')--query -05:00 time zone
Select Switchoffset (Sysdatetimeoffset (), ' +00:00 ')--query UTC time

---4, Todatetimeoffset function; Sets the date and time of the input is worth the time zone offset
Select Todatetimeoffset (Sysdatetimeoffset (),-10)

--5, DateAdd function; Allows you to increase the number of units specified for the specified date part to the date and time value entered.
Select DATEADD (year,1, ' 2016-06-28 ')--Specify a date plus 1 years 2017-06-28
Select DATEADD (quarter,1, ' 2016-06-28 ')-- Specify date plus 3 months 206-09-28
Select DATEADD (month,-1, ' 2016-06-28 ')--Specify a date minus 1 months 2016-05-28
Select DATEADD (day,1, ' 2016-06-28 ')--specified date plus 1 days 2016-06-29
Select DATEADD (week,1, ' 2016-06-28 ')--Specify date plus 7 days 2016-07-05
Select DATEADD (HOUR , 1, ' 2016-06-28 21:00:00.000 ')--Specify time plus 1 hours 2016-06-28 22:00:00.000
Select DATEADD (minute,1, ' 2016-06-28 21:00:00.000 ')--Specify time plus 1 minutes 2016-06-28 21:01:00.000
Select DATEADD (second,1, ' 2016-06-28 21:00:00.000 ')--Specify time plus 1 seconds 2016-06-28 21:00:01.000
Select DATEADD (millisecond,10, ' 2016-06-28 21:00:00.000 ')--Specify time plus 10 milliseconds 2016-06-28 21:00:00.010

--6, DateDiff (PART,VAL1,VAL2) function; Returns VAL2-VAL1 returns the number of units specified
Select DateDiff (year, ' 2011-06-28 ', ' 2017-06-28 ')
Select DateDiff (QUARTER, ' 2016-03-28 ', ' 2016-06-28 ')
Select DateDiff (MONTH, ' 2016-05-28 ', ' 2016-06-28 ')
Select DateDiff (Day, ' 2016-06-27 ', ' 2016-06-28 ')
Select DateDiff (WEEK, ' 2016-06-21 ', ' 2016-06-28 ')
Select DateDiff (HOUR, ' 2016-06-28 20:00:00.000 ', ' 2016-06-28 21:00:00.000 ')
Select DateDiff (MINUTE, ' 2016-06-28 21:01:00.000 ', ' 2016-06-28 21:00:00.000 ')
Select DateDiff (SECOND, ' 2016-06-28 21:00:01.000 ', ' 2016-06-28 21:00:00.000 ')
Select DateDiff (Millisecond, ' 2016-06-28 21:00:00.001 ', ' 2016-06-28 21:00:00.000 ')

--7, DATEPART function; Returns an integer part of a date or time part;
Select DATEPART (year, ' 2016-06-28 ')
Select DATEPART (MONTH, ' 2016-06-28 ')
Select DATEPART (WEEK, ' 2016-06-28 ')
Select DATEPART (Day, ' 2016-06-28 ')
Select DATEPART (HOUR, ' 2016-06-28 21:01:02 ')
Select DATEPART (MINUTE, ' 2016-06-28 21:01:02 ')
Select DATEPART (SECOND, ' 2016-06-28 21:01:02 ')
Select DATEPART (Millisecond, ' 2016-06-28 21:01:02:100 ')


--8, year, month, and day functions, as with the DATEPART function; Returns an integer of years, months, and dates
Select year (' 2016-06-28 ')
Select month (' 2016-06-28 ')
Select Day (' 2016-06-28 ')

-----9, Datename function, similar to DATEPART, but returns not a number but a name, returns the specified date name based on the language of the operating system, e.g. Datename (MONTH, ' 2016-06-28 ') in the English operating system returns June
Select Datename (year, ' 2016-06-28 ')
Select Datename (MONTH, ' 2016-06-28 ')
Select Datename (Day, ' 2016-06-28 ')
Select Datename (HOUR, ' 2016-06-28 21:01:02 ')
Select Datename (MINUTE, ' 2016-06-28 21:01:02 ')
Select Datename (SECOND, ' 2016-06-28 21:01:02 ')
Select Datename (Millisecond, ' 2016-06-28 21:01:02:100 ')


---10, isdate function; Determines whether the input string can be converted to a date or time; True is 1;false 0
Select ISDATE (' 2016-06-28 ')--true
Select ISDATE (' 2016-06-282 ')--false

Select ISDATE (' 2016-06-28 21:00:00.000 ')--true
Select ISDATE (' 2016-06-28 21:00:00.0000 ')--false

MS SQL Server Time function