SQL Query - Date, between dateadd problem

[Raghu]

Hello...
I am running into a problem while running a query..can some1 help..
this is the query :
**************
SELECT *
from Table S
where S.dtDate1 BETWEEN dateadd(year,1, dateadd(month,-1,getdate()))
AND dateadd(day,-1,(dateadd(mont h,1,dateadd(yea r,1,dateadd(mon th,-1,getdate())))) )
***************
(first part of the date calculation comes out to be '2005-05-01' and
second part is '2005-05-31')
Here is the interesting twist:
The query runs right if the first date in the between clause is
entered directly i.e. the query wud run right if i rite it as

SELECT *
from vCustomerProduc tDetail S
where S.dtDate1 BETWEEN '2005-05-01'
AND dateadd(day,-1,(dateadd(mont h,1,dateadd(yea r,1,dateadd(mon th,-1,getdate())))) )

The funny part is the ending date in the between part is depending on
the starting date if u notice..so if there is somethg rong in the
first part there shud be somethg rong with the second too if u get wat
i am saying but the query runs right with the second part left as it
is..
can some1 please help..

thanks
-Raghu

[Hugo Kornelis]

Re: SQL Query - Date, between dateadd problem

On 1 Jun 2004 11:33:04 -0700, Raghu wrote:
[color=blue]
>Hello...
>I am running into a problem while running a query..can some1 help..
>this is the query :
>************ **
>SELECT *
>from Table S
>where S.dtDate1 BETWEEN dateadd(year,1, dateadd(month,-1,getdate()))
>AND dateadd(day,-1,(dateadd(mont h,1,dateadd(yea r,1,dateadd(mon th,-1,getdate())))) )
>************** *
>(first part of the date calculation comes out to be '2005-05-01' and
>second part is '2005-05-31')
>Here is the interesting twist:
>The query runs right if the first date in the between clause is
>entered directly i.e. the query wud run right if i rite it as
>
>SELECT *
>from vCustomerProduc tDetail S
>where S.dtDate1 BETWEEN '2005-05-01'
>AND dateadd(day,-1,(dateadd(mont h,1,dateadd(yea r,1,dateadd(mon th,-1,getdate())))) )
>
>The funny part is the ending date in the between part is depending on
>the starting date if u notice..so if there is somethg rong in the
>first part there shud be somethg rong with the second too if u get wat
>i am saying but the query runs right with the second part left as it
>is..
>can some1 please help..
>
>thanks
>-Raghu[/color]

Hi Raghu,

You forgot to mention what exactly went wrong. That's usually needed to be
able to help.

In this case, however, I think I can guess what your problem is. Either
some or all rows with dtDate1 equal to May 1st are omitted.

The datetime datatype stores a combination of date and time. If you enter
only a date, a default time of midnight is assumed. Try the following:

SELECT convert(datetim e,'2004-05-01')

Your calculation is based on adding and subtracting whole days and months
to getdate(), a function that returns both current date and time. The time
is retained as it is. See for yourself:

SELECT dateadd(year,1, dateadd(month,-1,getdate()))
returns
2005-05-01 21:08:55.937

All dates in your database of May 1st, midnight are before this date, so
they are not selected. To include them as well, you need to trim off time
portion. This can be done by converting to character, using a style code
that leaves the time out. The char value will be converted back to
datetime, but the time portion is lost at that time.

SELECT dateadd(year, 1, convert (varchar,
dateadd(month,-1,getdate()), 106))
returns
2005-05-01 00:00:00.000

This suffices if all dates stored in dtDate1 are stored with the default
time (00:00:00:000). If other times are stored as well, you'll have to do
something about the end date as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)