469,632 Members | 1,730 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,632 developers. It's quick & easy.

SQL Query - Date, between dateadd problem

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(month,1,dateadd(year,1,dateadd(month,-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 vCustomerProductDetail S
where S.dtDate1 BETWEEN '2005-05-01'
AND dateadd(day,-1,(dateadd(month,1,dateadd(year,1,dateadd(month,-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
Jul 20 '05 #1
1 16995
On 1 Jun 2004 11:33:04 -0700, Raghu wrote:
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(month,1,dateadd(year,1,dateadd(month,-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 vCustomerProductDetail S
where S.dtDate1 BETWEEN '2005-05-01'
AND dateadd(day,-1,(dateadd(month,1,dateadd(year,1,dateadd(month,-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


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(datetime,'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)
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Mark | last post: by
1 post views Thread by Brian Jorgenson | last post: by
5 posts views Thread by Ian Davies | last post: by
2 posts views Thread by Thomas Beyerlein | last post: by
1 post views Thread by zsolt | last post: by
2 posts views Thread by zdk | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.