Advanced Search

SQL SERVER – Useful DateTime Functions to Find Specific Dates

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. ---Today
  2. SELECT GETDATE() 'Today'
  3. ----Yesterday
  4. SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
  5. ----First Day of Current Week
  6. SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
  7. ----Last Day of Current Week
  8. SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
  9. ----First Day of Last Week
  10. SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'
  11. ----Last Day of Last Week
  12. SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week'
  13. ----First Day of Current Month
  14. SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month'
  15. ----Last Day of Current Month
  16. SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month'
  17. ----First Day of Last Month
  18. SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month'
  19. ----Last Day of Last Month
  20. SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month'
  21. ----First Day of Current Year
  22. SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First Day of Current Year'
  23. ----Last Day of Current Year
  24. SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) 'Last Day of Current Year'
  25. ----First Day of Last Year
  26. SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) 'First Day of Last Year'
  27. ----Last Day of Last Year
  28. SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) 'Last Day of Last Year'

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.