Determining Month Ending Date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Determining Month Ending Date

Author

Topic

TSQLMan
Posting Yak Master

160 Posts

Posted - 2004-07-22 : 09:19:56

In a select statement I need to determine the last day of the month, based on a date selected from a table.

If the date were 6/01/2004 I need to return 6/30/2004.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-22 : 09:30:25

if DateVal is your date, then:

select dateadd(mm, datediff(mm, 0, DateVal)+1,0)-1

will return the last day of the month for DateVal.

- Jeff

craigh999
Starting Member

10 Posts

Posted - 2004-07-22 : 09:31:31

I found this, play with it to get the actual day you want

DECLARE @Date datetime
SET @Date = '2004/07/22'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'

Hope it helps

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-22 : 09:35:31

Long winded, but...


SELECT DATEADD(d,-1,'01/'+CONVERT(varchar,DATEPART(m,DATEADD(m,1,'04 jun 2004')))+'/'+CONVERT(varchar,DATEPART(yyyy,DATEADD(m,1,'04 jun 2004'))))

Basically, add a month to the current date, let's call it MyDate
Then build a date made up of '01'+month+year of MyDate
Then, subtract a day from MyDate.

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-22 : 09:37:23

quote:
Originally posted by jsmith8858

if DateVal is your date, then:

select dateadd(mm, datediff(mm, 0, DateVal)+1,0)-1

will return the last day of the month for DateVal.

- Jeff

LOL, nice use of DATEDIFF, much more elegant than my rambling code effort

Kristen
Test

22859 Posts

Posted - 2004-07-22 : 09:43:18

quote:
Originally posted by jsmith8858

select dateadd(mm, datediff(mm, 0, DateVal)+1,0)-1

If TSQLMan is using this in a WHERE clause as the upper limit I think he would be better off with

WHERE MyColumn < dateadd(mm, datediff(mm, 0, DateVal)+1,0)

rather than

WHERE MyColumn <= dateadd(mm, datediff(mm, 0, DateVal)+1,0)-1

to allow for the fact that MyColumn might have a time component.

Kristen

TSQLMan
Posting Yak Master

160 Posts

Posted - 2004-07-22 : 09:51:00

Thank You very much. I just did the following to determine a week Ending Date based on @date. Don't know why I didn't think to do the same for the month.

@WeenEnding = @Date - (datepart(dw,@Date) -7) AS WeekEnd

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources