Date formatting statement and SQL Server statement during SQL Server Query

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!