Several commonly used date functions of SQL Server (1)

The following table summarizes several time functions commonly used in SQL Server.

function	description
getdate ()	returns the current date and time
convert ()	display date/time in different formats
datediff ()	returns the time between two dates
datepart ()	Returns a separate part of the date/time
dateadd ()	add or subtract a specified interval from a date

This section focuses on the first two functions.

1. SQL Server getdate () Function

• This function is relatively simple and can easily return the current time and date from SQL Server.
• Instance:

Select getdate () as now

• You can also set the default value for fields when designing a database. When inserting data, if this field is not set, it is the current time.

Create Table userinfo (

IID int identity (1, 1) not null primary key,

Susername varchar (50 ),

Regtime datetime not null defaultGetdate ()

)

• When inserting data, if this field is not set, it is the current time.

2. Convert (data_type (length), date, style) Function

• This function can display a date in a specified format.Data_type (length) indicates the length of the data to be converted. Date indicates the value to be converted.StyleSpecifies the output format of the date/time.
• StyleThe value and output format are as follows:

Style ID	StyleFormat
100 or 0	Mon dd yyyy hh: miam (or pm)
101	Mm/DD/yy
102	YY. Mm. dd
103	Dd/mm/yy
104	Dd. mm. yy
105	DD-mm-yy
106	Dd mon YY
107	Mon DD, YY
108	Hh: mm: SS
109 or 9	Mon dd yyyy hh: MI: SS: mmmam (or pm)
110	Mm-dd-yy
111	YY/MM/dd
112	Yymmdd
113 or 13	Dd mon yyyy hh: mm: SS: Mmm (24 h)
114	Hh: MI: SS: Mmm (24 h)
120 or 20	Yyyy-mm-dd hh: MI: SS (24 h)
121 or 21	Yyyy-mm-dd hh: MI: Ss. Mmm (24 h)
126	Yyyy-mm-ddthh: mm: Ss. Mmm (no space)
130	Dd mon yyyy hh: MI: SS: mmmam
131	Dd/mm/yy hh: MI: SS: mmmam

• Several common conversion l instances:

Select convert (varchar (20), getdate (), 111)

Select convert (varchar (20), getdate (), 112)

Select convert (varchar (20), getdate (), 120)