Advanced Search

Getting Dates in Sql Server

/ Published in: SQL
Save to your folder(s)
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

Copy this code and paste it in your HTML 
  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'

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.