SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 last date of the month
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/02/2005 :  08:13:25  Show Profile  Visit AskSQLTeam's Homepage
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

USA
15676 Posts

Posted - 06/02/2005 :  08:16:23  Show Profile  Visit robvolk's Homepage
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.
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/02/2005 :  08:31:11  Show Profile  Visit Seventhnight's Homepage
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.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/02/2005 :  13:15:33  Show Profile
quote:
Originally posted by Seventhnight

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



It doesn't trim the timepart.

rockmoose
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/02/2005 :  13:20:03  Show Profile  Visit Seventhnight's Homepage
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.
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 06/02/2005 :  14:54:55  Show Profile
You are right Corey, just do DATEADD(dd,@offset,theDate).

rockmoose
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/02/2005 :  15:39:07  Show Profile
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
Go to Top of Page

chmcwill
Starting Member

1 Posts

Posted - 06/12/2005 :  17:10:58  Show Profile
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?
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 06/13/2005 :  13:48:21  Show Profile  Visit Seventhnight's Homepage
**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.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/13/2005 :  13:52:37  Show Profile
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
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 03/04/2006 :  03:44:27  Show Profile
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000