6. Basic SQL sorting (date and time data types, conversion functions) and SQL data types

6. Basic SQL sorting (date and time data types, conversion functions) and SQL data types
Date and Time Data Type

* System constant:

@ DATEFIRST (returns the current time)

DATEADD

Increase time

Syntax: DATEADD (datepart, number, date)

Select DATEADD (YEAR, 2, '1970-11-2 ')

 

DATEDIFF

Distance between two dates

Select DATEDIFF (YEAR, '2017-7-18 ', '2017-11-2 ')

 

DATENAME

Returns the number of blocks in a certain time value.

Select DATENAME (YEAR, '2017-7-8 ')

 

DATEPART

Select DATEPART (second, '2017-11-2 00:00:01. 123 ')

 

 

(DatenameThe returned string is datepart.Int is returned.Type)

Select DATENAME (weekday, '2017-11-2 ')

Select DATEPART (weekday, '2017-11-2 ')

 

DAY

Returns the day of the current month, and the int value.

Select DAY ('2017-11-2 ')

 

MONTH/YEAR

Similar to DAY

GETDATE

Get current server time

Select GETDATE ()

 

* GETUTCDATE is similar to GETDATE.

ISDATE

If the time format is used, "1" is returned"

Select ISDATE ('2014-2-29 ')

Select ISDATE ('2014-2-28 ')

 

SYSDATETIME

Obtain system time (precise but slow)

Select GETDATE ()

Select SYSDATETIME ()

 

Type conversion, Conversion Function

Cast, convert, parse

Select CAST (123 as varchar (20 ))

 

Select CONVERT (int, '123 ')

 

Parse cannot be used in version 08

 

Exercise

--- Cut the birthday

Select name, substring (cid,) + 'Year' + SUBSTRING (cid,) + 'month' + SUBSTRING (cid,) + 'day' from haha