By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,184 Members | 1,390 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,184 IT Pros & Developers. It's quick & easy.

SQL Query - Date, between dateadd problem

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.