Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to format the date when querying in SqlServer, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

The default date field datetime format for the Chinese version of SQL Server is yyyy-mm-dd Thh:mm:ss.mmm

For example:

Select getdate () 2004-09-12 11 Fringe 06purl 08.177

Sort out the date format conversion methods that may be often used in SQL Server:

Examples are as follows:

Select CONVERT (varchar, getdate (), 1211) 2004-09-1211: 06:08 select replace (CONVERT (varchar, getdate (),'-,'),','), (varchar (12), getdate (), 20040912110608 select CONVERT (varchar (12)) 2004-09-12 select CONVERT (varchar (12), getdate (), 112) 20040912 select CONVERT (varchar (12), getdate (), 102) 2004.09.12 select CONVERT (varchar (12) Select CONVERT (varchar (12), getdate (), 10312) select CONVERT (varchar (12), getdate (), 2004) 12.09.2004 select CONVERT (varchar (12), getdate (), 2004) 12-09-2004 select CONVERT (varchar (12), getdate (), 10610) 1209 2004 select CONVERT (varchar (12), getdate (), 107) 09 12, 2004 select CONVERT (varchar (12), getdate () 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) 1115 06getdate 08.177

The third parameter of Convert, the format of date type conversion:

For example, Convert (Char (10), GetDate (), 111)

101US mm/dd/yyyy 102ANSI yy.mm.dd 103UK / France dd/mm/yy 104Germany dd.mm.yy 105Italy dd-mm-yy 106107dd mon yy 107mon dd Yy 108-hh:mm:ss 109 (*) default + millisecond mon dd yyyy hh:mi:ss:mmmAM (or PM) 110US mm-dd-yy 111Japan yy/mm/dd 112ISO yymmdd 113( *) European default + millisecond dd mon yyyy hh:mm:ss:mmm (24h) 114-hh:mi:ss:mmm (24h) 120 (*) ODBC specification yyyy-mm-dd hh:mm:ss [.fff] 121 (*) ODBC specification (with Ms) 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, GETDATE (), 0): 05 16 2006 10:57AMSelect CONVERT (varchar, GETDATE (), 1): 05/16/06Select CONVERT (varchar, GETDATE (), 2): 06.05.16Select CONVERT (varchar, GETDATE (), 3): 16/05/06Select CONVERT (varchar, GETDATE, 4): 16.05.06Select CONVERT (varchar, GETDATE (), 5): 16-05-06Select CONVERT (varchar (100), GETDATE () 6): 16 05 06Select CONVERT (varchar, GETDATE (), 7): 0516, 06Select CONVERT (varchar, GETDATE (), 8): 10:57:46Select CONVERT (varchar, GETDATE (), 9): 0516 2006 10:57:46:827AMSelect CONVERT (varchar, GETDATE (), 10): 05-16-06Select CONVERT (varchar, GETDATE (), 11): 06/05/16Select CONVERT (varchar, GETDATE (), 12): 060516Select CONVERT (varchar (100), GETDATE () 13): 16 05 2006 10:57:46:937Select CONVERT (varchar, 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, GETDATE (), 22): 05lap 1606 10:57:47 AMSelect CONVERT (varchar (100), GETDATE () 23): 2006-05-16Select CONVERT (varchar, GETDATE (), 24): 10:57:47Select CONVERT (varchar, GETDATE (), 25): 2006-05-16 10:57:47.250Select CONVERT (varchar, GETDATE (), 100): 05 16 2006 10:57AMSelect CONVERT (varchar, GETDATE): 05/16/2006Select CONVERT (varchar, GETDATE): 2006.05.16Select CONVERT (varchar (100), GETDATE () 16/05/2006Select CONVERT: 16.05.2006Select CONVERT (varchar, GETDATE): 16-05-2006Select CONVERT (varchar, GETDATE): 16 05 2006Select CONVERT (varchar, GETDATE): 05 16, 2006Select CONVERT (varchar, GETDATE): 10:57:49Select CONVERT (varchar, GETDATE, 109): 05 16 2006 10:57:49:437AMSelect CONVERT (varchar GETDATE: 05-16-2006Select CONVERT: 2006/05/16Select CONVERT (varchar, GETDATE): 20060516Select CONVERT (varchar, GETDATE): 16 05 2006 10:57:49:513Select CONVERT (varchar, GETDATE): 10:57:49:547Select CONVERT: varchar, GETDATE (): 2006-05-16 10:57:49Select CONVERT (varchar, GETDATE) 2006-05-16 10:57:49.700Select CONVERT (varchar, GETDATE): 2006-05-16T10:57:49.827Select CONVERT (varchar, GETDATE, 1330): 18?

Commonly used:

Select CONVERT (varchar, GETDATE (), 24): 10:57:47Select CONVERT (varchar, GETDATE (), 108): 10:57:49Select CONVERT (varchar, GETDATE (), 12): 060516Select CONVERT (varchar, 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: let's introduce Sql Server to the date query, if the date in the table reaches a specific time

1. If the query date parameter is' 2017 Universe 02 Universe 21 minutes, and the field in the database table is' 2017-02-21 12% 34% 16. 963', then the date needs to be formatted before it can be queried, as follows

Select * from table t where t.date between CONVERT (datetime, '2017ap02) and CONVERT (datetime,' 2017Accord02) +'23 and CONVERT 59')

The scope of the query is: '2017-02-21 00Rd 000GRN 07AG02UniGUP 21 23Rd 59Rod 59 QR. This will solve the problem.

two。 Or use the dateadd method to add one day to the date, as follows

Select * from table t where t.date > = CONVERT (datetime, '2017 dateadd 02) and t.date < CONVERT (datetime, dateadd (day,1,'2017/02/21'))

The scope of the query is' 2017Universe 02max 21'.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.