Weird problem with SQL SP.

[vbSun]

Hi all..

I am trying to get the first and last dates of the previous month from today. The logic works fine on all dates, except for May 31st! Any idea?

declare @tmpdate as datetime
set @tmpdate = getdate()
select @tmpdate,dateadd(d,-datepart(d,@tmpdate),@tmpdate),dateadd(m,-1,dateadd(d,-datepart(d,@tmpdate),@tmpdate))+1

The result it is giving on all other dates other than May31st

2005-06-29 18:13:44.150
2005-05-31 18:13:44.150
2005-05-01 18:13:44.150

If the system date is May 31st, the result will be

2005-05-31 18:14:48.920
2005-04-30 18:14:48.920
2005-03-31 18:14:48.920

Any idea how this is happening?


------------------------------------------
The faulty interface lies between the chair and the keyboard.

 

[maswien]

it should be:

select getdate(),
       dateadd(d,-datepart(d,getdate()),getdate()),
       dateadd(m,-1,dateadd(d, 1 - datepart(d, getdate()), getdate()))