First and last date of a month in Sql

525
Views
1
Answers

How to get the First and last date of a month in MS Sql?

edit | flag
William Patrick
Asked on: Jul 27, 2011 at 8:07PM

1 Answers

51
4
51

This query will run based on Current 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.

**Last Day of Previous Month**
SELECT CONVERT
(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())),GETDATE()),101) ,'Last Day of Previous Month'

**First Day of Current Month**
SELECT CONVERT
(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GETDATE()),101) AS Date_Value,'First Day of Current Month' AS Date_Type

**Today**
SELECT CONVERT
(VARCHAR(25),GETDATE(),101) AS Date_Value, 'Today' AS Date_Type

**Last Day of Current Month**
SELECT CONVERT
(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))),DATEADD(mm,1,GETDATE())),101) ,'Last Day of Current Month'

**First Day of Next Month**
SELECT CONVERT
(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,GETDATE()))-1),DATEADD(mm,1,GETDATE())),101) ,
'First Day of Next Month'

The result will be
06/30/2011 Last Day of Previous Month
07/01/2011 First Day of Current Month
07/27/2011 Today
07/31/2011 Last Day of Current Month
08/01/2011 First Day of Next Month

edit | flag
Gokul A
Answered on: Jul 27, 2011 at 8:13PM

Post your Answer

Search

Welcome to Ask Amoeba!
This is 100% free and interactive site for sharing professional Questions and Answers, Opensource projects, Interview questions.
Learners, beginners, Experts stop and share your knowledge and ideas!

Browse Categories

Browse Tags