SQL statement Encyclopedia of Operation dates

SQL statement Encyclopedia for Action dates:

1. Show the first day of this month

SELECT DATEADD (Mm,datediff (Mm,0,getdate ()), 0)

Select CONVERT (Datetime,convert (varchar (8), GETDATE (),

120) + ' 01 ', 120)

2. Show the last day of this 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 ()) +1,0))

3. Last day of last month

SELECT DateAdd (Ms,-3,dateadd (Mm,datediff (Mm,0,getdate ()), 0)

4. This month's first Monday I

Select DATEADD (Wk,datediff (wk,0, DATEADD (Dd,6-datepart (

Day,getdate ()), GETDATE ()), 0)

5. The first day of the 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 ()) +1,0))

7. Last day of last year

SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()), 0)

8. The first day of the quarter

SELECT DATEADD (Qq,datediff (Qq,0,getdate ()), 0)

9. Monday of this week

SELECT DATEADD (Wk,datediff (Wk,0,getdate ()), 0)

10. Check this month's records

SELECT * FROM tablename where DATEPART (mm, thedate)

=datepart (mm, GETDATE ()) and DATEPART (yy, thedate)

= DATEPART (yy, GETDATE ())

11. Check this week's records

SELECT * FROM tablename where DATEPART (wk, thedate) = DATEPART

(wk, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GETDATE ())

12. Check the record note for this season: GETDATE () is a function to obtain the system time.

SELECT * FROM TableName where DATEPART (QQ, thedate) = DATEPART

(QQ, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GETDATE ())

13. Get total 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 ()), ' 1900-02-01 ')

14. Get the current day of the week

Datename (Weekday, GETDATE ())