Summary of SQL Server time format usage

--- First day of last month
Select convert (varchar (12), dateadd (mm, datediff (mm, 0, dateadd (mm,-1, getdate (), 0), 112)
--- Last day of last month
Select convert (varchar (12), dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate (), 0), 112)

1. display the first day of the month

Select dateadd (mm, datediff (mm, 0, getdate (), 0)
Select convert (datetime, convert (varchar (8), getdate (),
120) + '01', 120)
 

2. display the last day of the month

Select dateadd (day,-1, convert (datetime, convert
(Varchar (8), dateadd (month, 1, getdate (), 120) + '01', 120 ))
Select dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + ))
 

3. Last day of last month

Select dateadd (MS,-3, dateadd (mm, datediff (mm, 0, getdate (), 0 ))
 

4. the first Monday of this month

Select dateadd (wk, datediff (wk, 0, dateadd (DD, 6-datepart (
Day, getdate (), getdate (), 0)
 

5. The first day of this year

Select dateadd (YY, datediff (YY, 0, getdate (), 0)
 

6. The last day of the year

Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate () + ))
 

7. Last day of last year

Select dateadd (MS,-3, dateadd (YY, datediff (YY, 0, getdate (), 0 ))
 

8. The first day of this quarter

Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)
 

9. Monday of the week

Select dateadd (wk, datediff (wk, 0, getdate (), 0)
 

10. query records of this month

Select * From tablename where datepart (mm, thedate)
= Datepart (mm, getdate () and datepart (YY, thedate)
= Datepart (YY, getdate ())
 

11. query the records of this week

Select * From tablename where datepart (wk, thedate) = datepart
(Wk, getdate () and datepart (YY, thedate) = datepart (YY, getdate ())
 

12. query the record of this season. Note: getdate () Is the function for obtaining the system time.

Select * From tablename where datepart (QQ, thedate) = datepart
(Qq, getdate () and datepart (YY, thedate) = datepart (YY, getdate ())
 

13. Get the total number of days of the month:

Select datediff (DD, getdate (), dateadd
(Mm, 1, getdate ()))
 
Select datediff (day,
Dateadd (mm, datediff (mm, '', getdate ()),''),
Dateadd (mm, datediff (mm, '', getdate (), '2017-02-01 '))
 

14. Obtain the current day of the week

Datename (weekday, getdate ())
 

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

1. Select convert (varchar (12), getdate (), 101)
12/28/2007
2. Select convert (varchar (12), getdate (), 102)
2007.12.28
3. Select convert (varchar (12), getdate (), 103)
28/12/2007
4. Select convert (varchar (12), getdate (), 104)
28.12.2007
5) Select convert (varchar (12), getdate (), 105)
28-12-2007
6. Select convert (varchar (12), getdate (), 106)
28 12 2007
7. Select convert (varchar (12), getdate (), 107)
12 28,200 7
8. Select convert (varchar (12), getdate (), and 108)
10:06:33
9. Select convert (varchar (12), getdate (), 109)
12 28 2007 1
10. Select convert (varchar (12), getdate (), 110)
12-28-2007
11. Select convert (varchar (12), getdate (), 111)
2007/12/28
12. Select convert (varchar (12), getdate (), 112)
20071228
13. Select convert (varchar (12), getdate (), 113)
28 12 2007 1
14. Select convert (varchar (12), getdate (), 114)
10: 07: 36: 623
15. Select convert (varchar, getdate (), 120)
2007-12-28 10:08:56

Select convert (varchar (10), getdate (), 120)
2007-12-28

16. select Replace (replace (convert (varchar, getdate (), 120 ),'-',''),'',''),':','')
20071228101012

 

1. display the first day of the month
Select dateadd (mm, datediff (mm, 0, getdate (), 0)
Select convert (datetime, convert (varchar (8), getdate (), 120) + '01', 120)

2. display the last day of the month
Select dateadd (day,-1, convert (datetime, convert (varchar (8), dateadd (month, 1, getdate (), 120) + '01', 120 ))
Select dateadd (MS,-3, dateadd (mm, datediff (M, 0, getdate () + ))

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

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

5. The first day of this year
Select dateadd (YY, datediff (YY, 0, getdate (), 0)

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

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

8. The first day of this quarter
Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)

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

10. query records of this month
Select * From tablename where datepart (mm, thedate) = datepart (mm, getdate () and datepart (YY, thedate) = datepart (YY, getdate ())

11. query the records of this week
Select * From tablename where datepart (wk, thedate) = datepart (wk, getdate () and datepart (YY, thedate) = datepart (YY, getdate ())

12. query this season's records
Select * From tablename where datepart (QQ, thedate) = datepart (QQ, getdate () and datepart (YY, thedate) = datepart (YY, getdate ())
Specifically, getdate () is a function used to obtain the system time.

13. Get the total number of days of the month:
Select datediff (DD, getdate (), dateadd (mm, 1, getdate ()))

Select datediff (day,
Dateadd (mm, datediff (mm, '', getdate ()),''),
Dateadd (mm, datediff (mm, '', getdate (), '2017-02-01 '))

14. Obtain the current day of the week
Datename (weekday, getdate ())