DateAdd function on 30th and 31st of month

Hi Everyone,
When I run the dateadd function adding months on 30th and 31st of any
months returns the same result. I am OK with that because logically it
is correct.
If I have to do a reverse lookup for the date, how can I do that.
Here is the code sample...
select dateadd(mm,6,'2006-05-30 00:00:00.000')
select dateadd(mm,6,'2006-05-31 00:00:00.000')
-- 2006-11-30 00:00:00.000
-- 2006-11-30 00:00:00.000
select dateadd(mm,-6,'2006-11-30 00:00:00.000')
select dateadd(mm,-6,'2006-12-01 00:00:00.000')
--2006-05-30 00:00:00.000
--2006-06-01 00:00:00.000
The problem I have is when I do a reverse lookup I am missing all the
records which got created on
2006-05-31 00:00:00.000.
Thanks in Advance...
Thanks,
Ganesh
Ganesh wrote:
> Hi Everyone,
> When I run the dateadd function adding months on 30th and 31st of any
> months returns the same result. I am OK with that because logically it
> is correct.
> If I have to do a reverse lookup for the date, how can I do that.
> Here is the code sample...
> select dateadd(mm,6,'2006-05-30 00:00:00.000')
> select dateadd(mm,6,'2006-05-31 00:00:00.000')
> -- 2006-11-30 00:00:00.000
> -- 2006-11-30 00:00:00.000
> select dateadd(mm,-6,'2006-11-30 00:00:00.000')
> select dateadd(mm,-6,'2006-12-01 00:00:00.000')
> --2006-05-30 00:00:00.000
> --2006-06-01 00:00:00.000
> The problem I have is when I do a reverse lookup I am missing all the
> records which got created on
> 2006-05-31 00:00:00.000.
> Thanks in Advance...
> Thanks,
> Ganesh
>
This will show you the date of the last day of the month "six months ago":
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -5,
GETDATE())), 0))
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Please don't multi-post.
"Ganesh" <babuganesh2000@.gmail.com> wrote in message
news:1164661068.378626.271490@.l12g2000cwl.googlegr oups.com...
> Hi Everyone,
> When I run the dateadd function adding months on 30th and 31st of any
> months returns the same result. I am OK with that because logically it
> is correct.
> If I have to do a reverse lookup for the date, how can I do that.
> Here is the code sample...
> select dateadd(mm,6,'2006-05-30 00:00:00.000')
> select dateadd(mm,6,'2006-05-31 00:00:00.000')
> -- 2006-11-30 00:00:00.000
> -- 2006-11-30 00:00:00.000
> select dateadd(mm,-6,'2006-11-30 00:00:00.000')
> select dateadd(mm,-6,'2006-12-01 00:00:00.000')
> --2006-05-30 00:00:00.000
> --2006-06-01 00:00:00.000
> The problem I have is when I do a reverse lookup I am missing all the
> records which got created on
> 2006-05-31 00:00:00.000.
> Thanks in Advance...
> Thanks,
> Ganesh
>

DateAdd function on 30th and 31st of month

Hi Everyone,
When I run the dateadd function adding months on 30th and 31st of any
months returns the same result. I am OK with that because logically it
is correct.
If I have to do a reverse lookup for the date, how can I do that.
Here is the code sample...
select dateadd(mm,6,'2006-05-30 00:00:00.000')
select dateadd(mm,6,'2006-05-31 00:00:00.000')
-- 2006-11-30 00:00:00.000
-- 2006-11-30 00:00:00.000
select dateadd(mm,-6,'2006-11-30 00:00:00.000')
select dateadd(mm,-6,'2006-12-01 00:00:00.000')
--2006-05-30 00:00:00.000
--2006-06-01 00:00:00.000
The problem I have is when I do a reverse lookup I am missing all the
records which got created on
2006-05-31 00:00:00.000.
Thanks in Advance...
Thanks,
GaneshGanesh wrote:
> Hi Everyone,
> When I run the dateadd function adding months on 30th and 31st of any
> months returns the same result. I am OK with that because logically it
> is correct.
> If I have to do a reverse lookup for the date, how can I do that.
> Here is the code sample...
> select dateadd(mm,6,'2006-05-30 00:00:00.000')
> select dateadd(mm,6,'2006-05-31 00:00:00.000')
> -- 2006-11-30 00:00:00.000
> -- 2006-11-30 00:00:00.000
> select dateadd(mm,-6,'2006-11-30 00:00:00.000')
> select dateadd(mm,-6,'2006-12-01 00:00:00.000')
> --2006-05-30 00:00:00.000
> --2006-06-01 00:00:00.000
> The problem I have is when I do a reverse lookup I am missing all the
> records which got created on
> 2006-05-31 00:00:00.000.
> Thanks in Advance...
> Thanks,
> Ganesh
>
This will show you the date of the last day of the month "six months ago":
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DATEADD(mm, -5,
GETDATE())), 0))
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Please don't multi-post.
"Ganesh" <babuganesh2000@.gmail.com> wrote in message
news:1164661068.378626.271490@.l12g2000cwl.googlegroups.com...
> Hi Everyone,
> When I run the dateadd function adding months on 30th and 31st of any
> months returns the same result. I am OK with that because logically it
> is correct.
> If I have to do a reverse lookup for the date, how can I do that.
> Here is the code sample...
> select dateadd(mm,6,'2006-05-30 00:00:00.000')
> select dateadd(mm,6,'2006-05-31 00:00:00.000')
> -- 2006-11-30 00:00:00.000
> -- 2006-11-30 00:00:00.000
> select dateadd(mm,-6,'2006-11-30 00:00:00.000')
> select dateadd(mm,-6,'2006-12-01 00:00:00.000')
> --2006-05-30 00:00:00.000
> --2006-06-01 00:00:00.000
> The problem I have is when I do a reverse lookup I am missing all the
> records which got created on
> 2006-05-31 00:00:00.000.
> Thanks in Advance...
> Thanks,
> Ganesh
>