Commonly used ms SQL date-related scripts

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 pushAlgorithm)
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)

1. The first day of a month
Select dateadd (mm, datediff (mm, 0, getdate (), 0)

2. Monday of the week
Select dateadd (wk, datediff (wk, 0, getdate (), 0)

3. The first day of the year
Select dateadd (YY, datediff (YY, 0, getdate (), 0)

4. The first day of the quarter
Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)

5. midnight of the day
Select dateadd (DD, datediff (DD, 0, getdate (), 0)

6. Last day of last month
Select dateadd (MS,-3, dateadd (mm, datediff (mm, 0, getdate (), 0 ))

7. Last day of last year
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 ))

8. Last day of the month
Select dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + 1, 0 ))

9. The last day of the year
Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + 1, 0 ))

10. the first Monday of this month
Select dateadd (wk, datediff (wk, 0, dateadd (DD, 6-datepart (day, getdate (), getdate (), 0)

Returns the current date and time.
By using the getdate () function, you can obtain the current date and time. The getdate () function can be used as the default value of a datedime field. This is useful for saving the time when a record is inserted. To create a table with records containing the current date and time, you can add a datetime field and specify its default value as the return value of the function getdate (), as shown in the following figure:

Create Table site_log (
Username varchar (40 ),
Useractivity varchar (100 ),
Entrydate datetime default getdate ())

Conversion date and time
The Return Value of the getdate () function is only displayed in seconds. In fact, the internal time of the SQL Server can be accurate to milliseconds (to be exact, it can be accurate to 3.33 milliseconds ).
To get the date and time in different formats, you need to use the convert () function (). For example, when the current statement is executed, the display time includes milliseconds:

Select convert (varchar (30), getdate (), 9)