advanced code snippet search

Posted By

derebus on 08/13/10


Tagged


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

hchattaway
Tyster


Getting Dates in Sql Server

 / Published in: SQL
 

URL: http://blog.sqlauthority.com/2007/05/13/sql-server-query-to-find-first-and-last-day-of-current-month/

Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month.

Expand | Embed | Plain Text 
  1. DECLARE @mydate DATETIME
  2. SELECT @mydate = GETDATE()
  3. SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) ,
  4. 'Last Day of Previous Month'
  5. UNION
  6. SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
  7. 'First Day of Current Month' AS Date_Type
  8. UNION
  9. SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
  10. UNION
  11. SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) ,
  12. 'Last Day of Current Month'
  13. UNION
  14. SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) ,
  15. 'First Day of Next Month'
  16. UNION
  17. SELECT CONVERT(VARCHAR(25),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)),101) ,
  18. 'Last Day of Next Month'

Report this snippet  

You need to login to post a comment.