SQL syntax for datetime-type operations in SQL Server

To operate SQL server data over the past two days, you need to set the time, minute, and second of a date field, that is, '2017-11-07 16:41:35. the 033 'is changed to '2017-11-07 00:00:00', so I checked the SQL syntax for datetime-type operations on the Internet and found that implementing this function is very simple.

Update yourtable set yourdatecolumn = convert (char (11), yourdatecolumn, 120!

 

Note: Other datetime operation syntax (for conversion)

1. Date formatting
Declare @ DT datetime
Set @ dt = getdate ()
-- 1. Short Date Format: yyyy-m-d
Select Replace (convert (varchar (10), @ DT, 120), N'-0 ','-')
-- 2. Long Date Format: yyyy-mm-dd
-- A. method 1
Select stuff (convert (char (8), @ DT, 112), 5, 0, N 'Year'), 8, 0, N 'month') + N'
-- B. method 2
Select datename (year, @ DT) + n'year' + datename (month, @ DT) + N' + datename (day, @ DT) + N'
-- 3. Long Date Format: yyyy-mm-dd
Select datename (year, @ DT) + N 'Year' + Cast (datepart (month, @ DT) as varchar) + N 'month' + datename (day, @ DT) + n'day'
-- 4. Complete date + time format: yyyy-mm-dd hh: MI: SS: Mmm
Select convert (char (11), @ DT, 120) + convert (char (12), @ DT, 114)
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)