| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 06/02/2005 : 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
USA
15557 Posts |
Posted - 06/02/2005 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/02/2005 : 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
Sweden
3279 Posts |
Posted - 06/02/2005 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/02/2005 : 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
Sweden
3279 Posts |
Posted - 06/02/2005 : 14:54:55
|
You are right Corey, just do DATEADD(dd,@offset,theDate).
rockmoose |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
chmcwill
Starting Member
1 Posts |
Posted - 06/12/2005 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 06/13/2005 : 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 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
USA
6997 Posts |
Posted - 03/04/2006 : 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 |
|
|
| |
Topic |
|
|
|
last date of the month
Forum Locked
