Date formatting statement and SQL Server statement during SQL Server Query
The default datetime format of the Chinese version of SQL Server isyyyy-mm-dd Thh:mm:ss.mmm
For example:
select getdate() 2004-09-12 11:06:08.177
I sorted out the date format conversion methods that may be frequently used in SQL Server:
Example:
select CONVERT(varchar, getdate(), 120 ) 2004-09-12 11:06:08 select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),'-',''),' ',''),':','') 20040912110608 select CONVERT(varchar(12) , getdate(), 111 ) 2004/09/12 select CONVERT(varchar(12) , getdate(), 112 ) 20040912 select CONVERT(varchar(12) , getdate(), 102 ) 2004.09.12 select CONVERT(varchar(12) , getdate(), 101 ) 09/12/2004 select CONVERT(varchar(12) , getdate(), 103 ) 12/09/2004 select CONVERT(varchar(12) , getdate(), 104 ) 12.09.2004 select CONVERT(varchar(12) , getdate(), 105 ) 12-09-2004 select CONVERT(varchar(12) , getdate(), 106 ) 12 09 2004 select CONVERT(varchar(12) , getdate(), 107 ) 09 12, 2004 select CONVERT(varchar(12) , getdate(), 108 ) 11:06:08 select CONVERT(varchar(12) , getdate(), 109 ) 09 12 2004 1 select CONVERT(varchar(12) , getdate(), 110 ) 09-12-2004 select CONVERT(varchar(12) , getdate(), 113 ) 12 09 2004 1 select CONVERT(varchar(12) , getdate(), 114 ) 11:06:08.177
The format of the third Convert parameter for date type conversion:
For example, Convert (Char (10), GetDate (), 111)
101 us mm/dd/yyyy
102 ANSI yy. mm. dd 103 UK/French dd/mm/yy
104 German dd. mm. yy
105 Italy dd-mm-yy
106-dd mon yy
107-mon dd, yy
108-hh: mm: ss
109 (*) Default Value + millisecond mon dd yyyy hh: mi: ss: mmmAM (or PM)
110 US mm-dd-yy
111 yy/mm/dd in Japan
112 ISO yymmdd
113 (*) European default value + millisecond dd mon yyyy hh: mm: ss: mmm (24 h)
114-hh: mi: ss: mmm (24 h)
120 (*) ODBC specification yyyy-mm-dd hh: mm: ss [. fff]
121 (*) ODBC specification (with millisecond) yyyy-mm-dd hh: mm: ss [. fff]
126 (***) ISO8601 yyyy-mm-dd Thh: mm: ss: mmm (without spaces)
130 * Kuwait dd mon yyyy hh: mi: ss: mmmAM
131 * Kuwait dd/mm/yy hh: mi: ss: mmmAM
A very powerful date Formatting Function in SQL Server
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AMSelect CONVERT(varchar(100), GETDATE(), 1): 05/16/06Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AMSelect CONVERT(varchar(100), GETDATE(), 10): 05-16-06Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16Select CONVERT(varchar(100), GETDATE(), 12): 060516Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AMSelect CONVERT(varchar(100), GETDATE(), 23): 2006-05-16Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AMSelect CONVERT(varchar(100), GETDATE(), 101): 05/16/2006Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AMSelect CONVERT(varchar(100), GETDATE(), 110): 05-16-2006Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16Select CONVERT(varchar(100), GETDATE(), 112): 20060516Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AMSelect CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM
Frequently used:
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49Select CONVERT(varchar(100), GETDATE(), 12): 060516Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16select CONVERT(varchar(12) , getdate(), 112 ) : 20040912 Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
For example:
convert(varchar(100),a.makedate,120) as makedate
PS: The following section describes how to use SQL Server to query a date.
1. If the query date parameter is '2014/1/21' and the field in the database table is '2014/2/21 12:34:16. 123', You need to format the date to query it, as shown below:
select * from table t where t.date between CONVERT(datetime, '2017/02/21', 120) and CONVERT(datetime, '2017/02/21', 120)+' 23:59:59') ;
The query range is '2014/1/21 00:00:00 '~ '2014/1/21 23:59:59 'to solve the problem.
2. Alternatively, use the dateadd method to add the date to one day, as shown below:
select * from table t where t.date >= CONVERT(datetime, '2017/02/21') and t.date < CONVERT(datetime, dateadd(day,1,'2017/02/21'));
The query range is'2017/02/21' <= t.date < '2017/02/22'
In this way, the problem can also be solved.
Summary
The above is a date formatting statement for SQL Server query. I hope it will be helpful to you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!