SQL Server--time-date functions, type conversions

One, time and date function

1.

Set Datefirst 1--setting Monday as the first day
--datepart function to return a part of a time date
--parameter 1 refers to which part is returned, and DW represents DayOfWeek
--Parameter 2 refers to the time and date in which to return
--datefirst is a system constant, which needs to be added when used @@
SELECT @ @DATEFIRST as ' 1st day ', DATEPART (DW, GETDATE ()) as ' Today '
--getdate refers to getting the current system time at execution time
SELECT GETDATE ()--takes the current system time at execution time

2.

--dateadd how much time to add to or subtract from a section
--Parameter 1 is the part to be added or reduced
--Parameter 3 is the time date on which to increase or decrease
--Parameter 2 is how much to add or subtract
Select DATEADD (year,-2, ' 2015-11-20 ')
Select DATEADD (month,3, ' 2015-11-20 ')
Select DATEADD (day,5, ' 2015-11-20 ')

3.

--datediff calculated time difference different different, difference
--parameter 1 refers to which part is the algorithm
--Parameter 2 is the start time date
--Parameter 3 is the end date
Select DATEDIFF (year, ' 2011-1-2 ', ' 2015-2-2 ')--years Difference
Select DATEDIFF (Month, ' 2011-1-2 ', ' 2015-2-2 ')--month difference (count middle all months)
Select DATEDIFF (Day, ' 2011-1-2 ', ' 2015-2-2 ')--diurnal difference (counting the middle of all days)

4, calculate the number of days of love
DECLARE @startday varchar (50)--you need to add a data type when declaring a variable, before the variable name plus @
Set @startday = ' 2008-8-8 '--sets the value of the variable
--getdate is the time to get the current system
Select DATEDIFF (Day, @startday, GETDATE ()) as Love days

5.

--datepart returns a portion of a time date
--Parameter 1 is which part of the return
--the date on which the parameter 2 is calculated
Select DATEPART (year, ' 2008-8-8 ')--return years
Select DATEPART (DY, ' 2008-8-8 ')--return to dayofyear the day of the year
Select DATEPART (QQ, ' 2008-8-8 ')--return to quarterly total of four quarters

--day Month is equivalent to the datepart above
--Return day, month, year
Select Day (' 2008-8-8 ')
Select year (' 2008-8-8 ')
Select month (' 2008-8-8 ')

6.

--isdate determine if the time date format is correct
--The return value of 1 indicates correct
--A return value of 0 indicates an error
Select ISDATE (' 2012-2-29 ')
Select ISDATE (' 2008-80-8 ')

--sysdatetime system Precise Time
Select Sysdatetime ()
SELECT GETDATE ()--the difference is accurate or not

Second, type conversion function

--Data type conversion cast convert
--cast writes the converted value + as + the type converted to
Select CAST (1.73 as int)
Select CAST (1.73 as varchar (50))
Select CAST (1.73333333 as Decimal (18,2))
Select cast (CAST (' 1.78 ' as Decimal (18,2) as int)
Select CAST (' 1.78 ' as Decimal (18,2))
The--convert parameter 1 is the type that needs to be converted, and parameter 2 is the value that needs to be converted
Select CONVERT (int, ' 87 ')
Select CONVERT (Decimal (18,2), ' 87.8787 ')--automatically rounded when the exact number of digits is later
Select CONVERT (varchar (50), 342523.234)

SQL Server--time-date functions, type conversions