SQL time and date formatting query statement Daquan

Let's take a look at the functions used.

CONVERT in SQL

Format:

CONVERT (data_type, expression [, style])

Note:

This style is generally in the time type (datetime, smalldatetime) and string type (nchar, nvarchar, char, varchar)
It is used only when they are converted to each other.

Example:

The code is as follows:	Copy code
Select convert (varchar (30), getdate (), 101) now

Result:

Now
---------------------------------------
| 09/15/2001

Parameter table

Style ID	Style format
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

SQL GETDATE () function

The GETDATE () function returns the current time and date from SQL Server.

Syntax

GETDATE () instance

Example 1

Use the following SELECT statement:

The code is as follows:	Copy code
Select getdate () AS CurrentDateTime Result: CurrentDateTime 16:25:46. 635

Note: the above time is accurate to milliseconds.

The code is as follows:

Copy code

Select CONVERT (varchar (100), GETDATE (), 0): 05 16 2006 AM Select CONVERT (varchar (100), GETDATE (), 1): 05/16/06 Select CONVERT (varchar (100), GETDATE (), 2): 06.05.16 Select CONVERT (varchar (100), GETDATE (), 3): 16/05/06 Select CONVERT (varchar (100), GETDATE (), 4): 16.05.06 Select CONVERT (varchar (100), GETDATE (), 5): 16-05-06 Select CONVERT (varchar (100), GETDATE (), 6): 16 05 06 Select CONVERT (varchar (100), GETDATE (), 7): 05 16, 06 Select CONVERT (varchar (100), GETDATE (), 8): 10:57:46 Select CONVERT (varchar (100), GETDATE (), 9): 05 16 2006 10: 57: 46: 827AM Select CONVERT (varchar (100), GETDATE (), 10): 05-16-06 Select CONVERT (varchar (100), GETDATE (), 11): 06/05/16 Select CONVERT (varchar (100), GETDATE (), 12): 060516 Select CONVERT (varchar (100), GETDATE (), 13): 16 05 2006 10: 57: 46: 937 Select CONVERT (varchar (100), GETDATE (), 14): 10: 57: 46: 967 Select CONVERT (varchar (100), GETDATE (), 20): 10:57:47 Select CONVERT (varchar (100), GETDATE (), 21): 10:57:47. 157 Select CONVERT (varchar (100), GETDATE (), 22): 05/16/06 10:57:47 AM Select CONVERT (varchar (100), GETDATE (), 23): Select CONVERT (varchar (100), GETDATE (), 24): 10:57:47 Select CONVERT (varchar (100), GETDATE (), 25): 10:57:47. 250 Select CONVERT (varchar (100), GETDATE (), 100): 05 16 2006 AM Select CONVERT (varchar (100), GETDATE (), 101): 05/16/2006 Select CONVERT (varchar (100), GETDATE (), 102): 2006.05.16 Select CONVERT (varchar (100), GETDATE (), 103): 16/05/2006 Select CONVERT (varchar (100), GETDATE (), 104): 16.05.2006 Select CONVERT (varchar (100), GETDATE (), 105): 16-05-2006 Select CONVERT (varchar (100), GETDATE (), 106): 16 05 2006 Select CONVERT (varchar (100), GETDATE (), 107): 05 16,200 6 Select CONVERT (varchar (100), GETDATE (), 108): 10:57:49 Select CONVERT (varchar (100), GETDATE (), 109): 05 16 2006 10: 57: 49: 437AM Select CONVERT (varchar (100), GETDATE (), 110): 05-16-2006 Select CONVERT (varchar (100), GETDATE (), 111): 2006/05/16 Select CONVERT (varchar (100), GETDATE (), 112): 20060516 Select CONVERT (varchar (100), GETDATE (), 113): 16 05 2006 10: 57: 49: 513 Select CONVERT (varchar (100), GETDATE (), 114): 10: 57: 49: 547 Select CONVERT (varchar (100), GETDATE (), 120): 10:57:49 Select CONVERT (varchar (100), GETDATE (), 121): 10:57:49. 700 Select CONVERT (varchar (100), GETDATE (), 126): 2006-05-16T10: 57: 49.827 Select CONVERT (varchar (100), GETDATE (), 130): 18 ???? ?????? 1427 10: 57: 49: 907AM Select CONVERT (varchar (100), GETDATE (), 131): 18/04/1427 10: 57: 49: 920AM

Frequently used:

The code is as follows:	Copy code
Select CONVERT (varchar (100), GETDATE (), 8): 10:57:46 Select CONVERT (varchar (100), GETDATE (), 24): 10:57:47 Select CONVERT (varchar (100), GETDATE (), 108): 10:57:49 Select CONVERT (varchar (100), GETDATE (), 12): 060516 Select CONVERT (varchar (100), GETDATE (), 23):