6, SQL Basic collation (date time data type, conversion function)

Date Time Data type

* System constants:

@ @DATEFIRST (back to current time)

DATEADD

Add Time

Syntax: DATEADD (datepart, number, date)

Select DATEADD (year,2, ' 2013-11-2 ')

DATEDIFF

Distance between two dates

Select DATEDIFF (year, ' 2011-7-18 ', ' 2014-11-2 ')

Datename

Returns a time value in which you want to get the number of a block type

Select Datename (year, ' 2011-7-8 ')

DATEPART

Select DATEPART (Second, ' 2014-11-2 00:00:01.1234556 ')

(Datename returns a string, DatePart the return is an int type)

Select Datename (Weekday, ' 2014-11-2 ')

Select DATEPART (Weekday, ' 2014-11-2 ')

Day

Returns the day of the month in which the date is returned, an int value

Select Day (' 2014-11-2 ')

Month/year

Similar to day

GETDATE

Gets the current time of the server

Select GETDATE ()

*getutcdate is similar to getdate.

ISDATE

is the time format to return "1"

Select ISDATE (' 2013-2-29 ')

Select ISDATE (' 2013-2-28 ')

Sysdatetime

Get system time (precise, but slow)

Select GETDATE ()

Select Sysdatetime ()

Type conversions, conversion functions

Cast,convert,parse

Select CAST (123 as varchar )

Select CONVERT (int, ' 123 ')

Parse is not available in version 08

Practice

---intercept birthdays

Select Name,substring (cid,7,4) + ' year ' +substring (cid,11,2) + ' month ' +substring (cid,13,2) + ' Day ' from haha

6, SQL Basic collation (date time data type, conversion function)