SQL Server convert datetime format conversion

Convert (, 120) 100 or 0 mon dd yyyy hh: miam (or PM) 101mm/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) 110mm-dd-yy 111 YY/MM/DD 112 yymmdd 113 or 13 dd mon yyyy hh: mm: SS: Mmm (24 h) 114 hh: MI: SS: mm (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

 

 

1st Page SQL Server convert datetime format conversion

Set the value of the [datetime] field of the table in SQL Server to '2017-11-07 16:41:35. change 033' to '2017-11-07 00:00:00 'to delete the time, minute, and second. [datetime] the field must be of the datetime type. update table set [datetime] = convert (char (11), [datetime], 120)

Obtain the current date and convert it to the required datetime format using convert.

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,200 4

------------------------------------------------------------

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

------------------------------------------------------------

Declare @ datetime -- defines a datetime variable

Set @ datetime = getdate (); -- Obtain the current system time and assign it to the @ datetime Field

-- Short Date Format: yyyy-m-d

Select Replace (convert (varchar (10), @ datetime, 120), N'-0 ','-')

-- Long Date Format: yyyy-mm-dd

Select stuff (convert (char (8), @ datetime, 112), 5, 0, N 'Year'), 8, 0, N 'month') + N'

-- Long Date Format: yyyy-mm-dd

Select datename (year, @ datetime) + N 'Year' + Cast (datepart (month, @ datetime) as varchar) + N 'month' + datename (day, @ datetime) + n'day'

-- Complete date + time format: yyyy-mm-dd hh: MI: SS: Mmm

Select convert (char (11), @ datetime, 120) + convert (char (12), @ datetime, 114)

Bytes ------------------------------------------------------------------------------------------------

2. Date Calculation

Declare @ DT datetime

Set @ dt = getdate ()

Declare @ number int

Set @ number = 3

-- 1. Specify the first or last day of the year

-- A. The first day of the year

Select convert (char (5), @ DT, 120) + '1-1'

-- B. The last day of the year

Select convert (char (5), @ DT, 120) + '12-31'

-- 2. Specify the first or last day of the quarter where the date is located.

-- A. The first day of the quarter

Select convert (datetime, convert (char (8), dateadd (month, datepart (quarter, @ DT) * 3-month (@ DT)-2, @ DT), 120) + '1 ')

-- B. The last day of the quarter)

Select convert (datetime, convert (char (8), dateadd (month, datepart (quarter, @ DT) * 3-month (@ DT), @ DT), 120)

+ Case when datepart (quarter, @ DT) in (1, 4)

Then '31 'else' 30' end)

-- C. The last day of the quarter (Direct Algorithm)

Select dateadd (day,-1, convert (char (8), dateadd (month, 1 + datepart (quarter, @ DT) * 3-month (@ DT ), @ DT), 120) + '1 ')

-- 3. Specify the first or last day of the month in which the date is located.

-- A. The first day of the month

Select convert (datetime, convert (char (8), @ DT, 120) + '1 ')

-- B. The last day of the month

Select dateadd (day,-1, convert (char (8), dateadd (month, 1, @ DT), 120) + '1 ')

-- C. The last day of the month (the easy-to-use error method)

Select dateadd (month, 1, dateadd (day,-day (@ DT), @ DT ))

-- 4. Specify any day of the week of the date

Select dateadd (day, @ number-datepart (weekday, @ DT), @ DT)

-- 5. Specify any day of the week of the date

-- A. Sunday is the 1st day of a week.

Select dateadd (date, @ number-(datepart (weekday, @ DT) + DATEFIRST-1) % 7, @ DT)

-- B. Monday is the 1st day of a week.

Select dateadd (day, @ number-(datepart (weekday, @ DT) + DATEFIRST-2) % 7-1, @ DT) (Source: Skynet)