last date of the month - 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

Old Forums

CLOSED - General SQL Server

last date of the month

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-06-02 : 08:13:25

Prashant writes "i have to add offset day to the activity date to get planned date

i can do that by using dateadd. But if activity date is 29 and offset day is 5 then planned activity date will give ????
Think last date of the month is 30 then i have to get the answer as 5 but 29+5=34. then how to calculate this one.

Please help me out.

Thanks in advance.
Prashant R"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-06-02 : 08:16:23

To get the last day of the month:

SELECT DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, dateCol)+1, 0) FROM myTable

That formula finds the difference in months, adds 1 month (gives the first day of the next month), then subtracts one day. You can alter it to add or subtract any extra days you need. If you need the 5th day of the following month, for example, change the -1 to 4.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-02 : 08:31:11

how about:
Select dateadd(mm,1,dateCol - day(dateCol)) From myTable

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-02 : 13:15:33

quote:
Originally posted by Seventhnight

how about:
Select dateadd(mm,1,dateCol - day(dateCol)) From myTable

It doesn't trim the timepart.

rockmoose

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-02 : 13:20:03

true... though I don't think that the time is whats really in question.

I don't understand why he can't just do: dateadd(dd,offset,dateCol)??

why does he need to know the month end?

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-06-02 : 14:54:55

You are right Corey, just do DATEADD(dd,@offset,theDate).

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-02 : 15:39:07

That's what I said....

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

chmcwill
Starting Member

1 Post

Posted - 2005-06-12 : 17:10:58

The short version, dateadd(mm,1,dateCol - day(dateCol)), does not work in all cases. For instance, if you take 03/12/2005 as @datecol, the result is 03/28/2005 instead of 03/31/2005 as it should be. This is because dateCol - day(dateCol) gives 02/28/2005.

I tried to use dateadd(dd,offset,dateCol) but could not work out what offset was defined as. Anyone know what offset is defined as?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-13 : 13:48:21

**fixed:

Select dateadd(mm,1,dateCol - day(dateCol)+1)-1,

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-13 : 13:52:37

Blog modified

http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx

Thanks For pointing that out

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-04 : 03:44:27

quote:
Originally posted by Seventhnight

**fixed:

Select dateadd(mm,1,dateCol - day(dateCol)+1)-1,

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

I know I am bumping an old thread, but since this is in Kristen's "sticky" and in Brett's blog, I thought I would add another method of finding the last day of the month, and point out a minor bug in this method.

As you can see from the script results below, there is an overflow that occurs anytime in months 1753/01 and 9999/12 when you use
dateadd(mm,1,dateCol - day(dateCol)+1)-1

The method I am posting also has the advantage of setting the time part to 00:00:00.000. It is another variation on using nested DATEADD/DATDIFF functions:
dateadd(mm,datediff(mm,-1,DT),-1)

This also works fine:
dateadd(mm,datediff(mm,30,DT),30)

Here is a variation to get the last day of the year:
dateadd(yy,datediff(yy,-1,DT),-1)

I saw this method is someone else's post, but I'm sorry I can't remember who to give credit to. I'm not borrowing it, I'm stealing it outright.


print 'DATEADD/DATDIFF Method'
select
	M1 = dateadd(mm,datediff(mm,-1,dt),-1)
from
(
select DT = convert(datetime,'20050101')	union all
select DT = '20040130'	union all
select DT = '20040228'	union all
select DT = '19000101 12:30'	union all
select DT = '18990101'	union all
select DT = '18991231'	union all
select DT = '17530101'	union all
select DT = '99991231 23:59:59.997'
) a

go
print 'DATEADD/DAY Method'

select
	M2=dateadd(mm,1,dateCol - day(dateCol)+1)-1
from
(
select dateCol = convert(datetime,'20050101')	union all
select dateCol = '20040130'	union all
select dateCol = '20040228'	union all
select dateCol = '19000101 12:30'	union all
select dateCol = '18990101'	union all
select dateCol = '18991231'	union all
select dateCol = '17530101'	union all
select dateCol = '99991231 23:59:59.997'
) a

Results:


DATEADD/DATDIFF Method
M1                                                     
------------------------------------------------------ 
2005-01-31 00:00:00.000
2004-01-31 00:00:00.000
2004-02-29 00:00:00.000
1900-01-31 00:00:00.000
1899-01-31 00:00:00.000
1899-12-31 00:00:00.000
1753-01-31 00:00:00.000
9999-12-31 00:00:00.000

(8 row(s) affected)

DATEADD/DAY Method
M2                                                     
------------------------------------------------------ 
2005-01-31 00:00:00.000
2004-01-31 00:00:00.000
2004-02-29 00:00:00.000
1900-01-31 12:30:00.000
1899-01-31 00:00:00.000
1899-12-31 00:00:00.000

(7 row(s) affected)

Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type datetime.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources