Article From:https://www.cnblogs.com/hongboiiii/p/9971845.html

In this article, the date obtained by GetDate () consists of two parts: today’s date and then’s time: Select GetDate ().
With DateName (), you can get the corresponding year, month and day, and then connect them together.

Select Datename(year,GetDate())+’-‘+Datename
(month,GetDate())+’-‘+Datename(day,GetDate())

In addition, DateName () can also get hours, time, seconds, days of the week, weeks, respectively, as follows:

Select Datename(hour,GetDate())
Select Datename(minute,GetDate())
Select Datename(second,GetDate())
Select Datename(weekDay,GetDate())
Select Datename(week,GetDate())

SQLThe default format for the date type DateTime is yyyy-mm-dd hh:mi:ss:Mmm, but in most cases we just want to get his date part, not the later time. In the previous article, we mentioned using Datename () function to intercept and spell.Take out the date that does not contain the time part. Now let’s say another way to get the date that does not contain the time more easily.

Using the Convert () function:

select convert(char(10),GetDate(),120) as Date

* The third parameter is used to set the display style of date type data. The following are the parameters of several styles:

100   mm dd yyyy

101   mm/dd/yyyy

102   yyyy.mm.dd

103   dd/mm/yyyy

106   dd mm yyyy

108   hh:mi:ss(Time)

111   yyyy/mm/dd

112   yyyymmdd

120   yyyy-mm-dd

 

Copy Code uuuuuuuuuuuu
1 SELECT CONVERT(varchar(100), GETDATE(), 0) 05 9 2011 9:12AM
2  SELECT CONVERT(varchar(100), GETDATE(), 1) 05/09/11
3 SELECT CONVERT(varchar(100), GETDATE(), 2) 11.05.09
4 SELECT CONVERT(varchar(100), GETDATE(), 3) 09/05/11
5 SELECT CONVERT(varchar(100), GETDATE(), 4) 09.05.11
6 SELECT CONVERT(varchar(100), GETDATE(), 5) 090511
7 SELECT CONVERT(varchar(100), GETDATE(), 6) 09 05 11
8 SELECT CONVERT(varchar(100), GETDATE(), 7) 05 09, 11
9 SELECT CONVERT(varchar(100), GETDATE(), 8) 09:13:14
10 SELECT CONVERT(varchar(100), GETDATE(), 9) 05 9 2011 9:13:14:670AM11 SELECT CONVERT(varchar(100), GETDATE(), 10) 05-09-1112 SELECT CONVERT(varchar(100), GETDATE(), 11) 11/05/0913 SELECT CONVERT(varchar(100), GETDATE(), 12) 11050914 SELECT CONVERT(varchar(100), GETDATE(), 13) 09 05 2011 09:13:14:67015 SELECT CONVERT(varchar(100), GETDATE(), 14) 09:13:14:67016 SELECT CONVERT(varchar(100), GETDATE(), 20) 2011-05-09 09:13:1417 SELECT CONVERT(varchar(100), GETDATE(), 21) 2011-05-09 09:13:14.67018 SELECT CONVERT(varchar(100), GETDATE(), 22) 05/09/11 9:15:33 AM19 SELECT CONVERT(varchar(100), GETDATE(), 23) 2011-05-0920 SELECT CONVERT(varchar(100), GETDATE(), 24) 09:15:3321 SELECT CONVERT(varchar(100), GETDATE(), 25) 2011-05-09 09:15:33.14022 SELECT CONVERT(varchar(100), GETDATE(), 100) 05 9 2011 9:15AM23 SELECT CONVERT(varchar(100), GETDATE(), 101) 05/09/201124 SELECT CONVERT(varchar(100), GETDATE(), 102) 2011.05.0925 SELECT CONVERT(varchar(100), GETDATE(), 103) 09/05/201126 SELECT CONVERT(varchar(100), GETDATE(), 104) 09.05.201127 SELECT CONVERT(varchar(100), GETDATE(), 105) 09-05-201128 SELECT CONVERT(varchar(100), GETDATE(), 106) 09 05 201129 SELECT CONVERT(varchar(100), GETDATE(), 107) 05 09, 201130 SELECT CONVERT(varchar(100), GETDATE(), 108) 09:16:3831 SELECT CONVERT(varchar(100), GETDATE(), 109) 05 9 2011 9:16:38:543AM32 SELECT CONVERT(varchar(100), GETDATE(), 110) 05-09-201133 SELECT CONVERT(varchar(100), GETDATE(), 111) 2011/05/0934 SELECT CONVERT(varchar(100), GETDATE(), 112) 2011050935 SELECT CONVERT(varchar(100), GETDATE(), 113) 09 05 2011 09:17:19:85736 SELECT CONVERT(varchar(100), GETDATE(), 114) 09:17:19:85737 SELECT CONVERT(varchar(100), GETDATE(), 120) 2011-05-09 09:17:1938 SELECT CONVERT(varchar(100), GETDATE(), 121) 2011-05-09 09:17:19.85739 SELECT CONVERT(varchar(100), GETDATE(), 126) 2011-05-09T09:17:19.85740 SELECT CONVERT(varchar(100), GETDATE(), 130) 6 ????? ??????? 1432 9:17:19:857AM41 SELECT CONVERT(varchar(100), GETDATE(), 131) 6/06/1432 9:17:19:857AM
Copy Code uuuuuuuuuuuu
Link of this Article: SQL time format

Leave a Reply

Your email address will not be published. Required fields are marked *