Need to retrieve dates within 24 hours from current date

[rorymo]

SQL Server 2000
BOXI
I need to check whether a particular date in our database is (within) 24 hours of the current date, and use this as part of the record selection criteria for my report.

The date field is stored as a number in the database. (we did not design the db, so we have no control over this.) This field is used to record a date/time in the future when a particular action will take place. My report needs to show those actions that will take place in the next 24 hours.

I am using this statement below as part of the record selection criteria, which seems to work, except it leaves out a record with that has the date/time of 11/9/2007 12:00:00AM. Records that have other times are picked up. The report shows all of the records that I am expecting to see except for that one record.

(UtcToDateTime((DateAdd("s",({chg.need_by}),cdatetime(1970,01,01,00,00,00)))) >= (currentdatetime)) and
(UtcToDateTime((DateAdd("s",({chg.need_by}),cdatetime(1970,01,01,00,00,00)))) <= (currentdatetime +1))

I have to use the UtcToDateTime function because the Crystal is not taking DST/CST time changes into consideration, so depending on the time of year, the dates in the database display on the report as the correct time, or one hour off. This function does the time change conversion for us.
We use the dateadd to convert the number to a date.

If I use anything else, I get a syntax error.

I have tried lots of different things, so any help with this would be greatly appreciated.

Thanks in advance,
Rory

 

[lbass]

Have you tried:

UtcToDateTime(DateAdd("s",{chg.need_by},cdatetime(1970,01,01,00,00,00)) >= currentdate and
UtcToDateTime(DateAdd("s",{chg.need_by},cdatetime(1970,01,01,00,00,00)) < currentdate +1

PS. More parens are not necessarily better

-LB

 

[rorymo]

Hi LB,
Thanks for the reply.
I copied and pasted what you suggested and it keeps telling me that the ")" is missing. As soon as I add one back in, it tells me that another one is missing. I ran it with the original statement except for the "=" sign in the last UTCToDateTime, and I still didn't get that record to show up.
I'm thinking that it may be something to do with how currentdate is looking at it, but I really can't find much on what arguments currentdate will accept. (I mean that maybe I should re-define the "+1" in hours but I don't know how) And then again, I may be way off base
Rory

 

[lbass]

Oops, sometimes more parens are better:

UtcToDateTime(DateAdd("s",{chg.need_by},cdatetime(1970,01,01,00,00,00))) >= currentdate and
UtcToDateTime(DateAdd("s",{chg.need_by},cdatetime(1970,01,01,00,00,00))) < currentdate +1

Note that this comparison is to currentdate, not currentdatetime.

-LB

 

[rorymo]

Thanks LB!
I totally missed the currentdate as opposed to currentdatetime. Once I used the currentdate, it worked perfectly and I got all the records I expected. So now I can have a nice weekend and hope you have one too.
Rory