Conversion Data of SQL Server-convert datetime format

In SQL Server, I often need to perform some time-type field conversions, but I don't quite remember it. So I collected the following sqlserverconvert datetime-related materials and posted them on my site, it is convenient for you to search for it later. I hope it will be helpful to you.

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 push 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 (day, @ 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)