Showing records from GETDATE +30 SQL not working,

[jmiller79]

This code was working until 3 days ago. i have no clue why. Does anyone see anything wrong with it?. Thanks a bunch.

SELECT Holiday, HolDate, PERSON
FROM Holidays
WHERE ( Convert(char(20),HOLDATE,101) BETWEEN Convert(char (20),GETDATE(),101)  AND Convert(char (20),GETDATE()+30,101))
AND PERSON = 'TRUCK'
ORDER BY HOLDATE ASC

 

[JamesLean]

I take it you are trying to search between today and today + 30 but not include the time portion? ie '2004-12-02' and '2005-01-01'.

I would avoid that charcter conversion:

SELECT Holiday, HolDate, PERSON
FROM Holidays
WHERE HOLDATE BETWEEN DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0) AND DATEADD(dd, DATEDIFF(dd, 0, GETDATE() + 30), 0)
AND PERSON = 'TRUCK'
ORDER BY HOLDATE

--James

 

[vongrunt]

Convert( 101 ) does mm/dd/yyyy so things naturally went bonkers 30 days before new year...

 

[ESquared]

Just get rid of the convert on your field, then use >= and < instead of between which is inclusive, note the +31.

WHERE HOLDATE >= Convert(char (20),GETDATE(),101) AND HOLDATE < Convert(char (20),GETDATE()+31,101))

This will perform a ton faster as well (not having to do a function on each row in your database, allowing use of indexes).

How you actually get the 12am dates for the comparison is a matter of style and preference.

JamesLean,

For what it's worth, your query will improperly miss all times on the 30th day after 12am.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[JamesLean]

Oops yes, you're quite right. I would use the two separate statements like your query. However I think I would still use the DATEADD technique and keep the values as datetime rather than char - just seems more logical to me!

If you did want to use the cast to character data method then I would use style 112 as it avoids any possible ambiguity with date formats (dmy, mdy etc.)

--James

 

[ESquared]

Right...the conversion is a matter of style and preference. I just used the conversion already presented in the thread. The dateadd technique is, to me, easier to get right and avoid making mistakes.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)