Report base on 24 hours UK time. 1

[bkno]

I am using Crystal 9 and accessing a Remedy database. I am creating a report that will collect data based on table Create_Time from midnight to midnight UK time. The report will be run in the morning so I need time parameter or formula that resemble (create_time > 7:00PM two days ago and Create_Time < 7:00PM yesterday). What is the best solution for the situation.

 

[synapsevampire]

I would think that you can just create a formula which adds the appropriate amount of time to the create date (this assumes that what you're trying to say is that the data is NOT stored in UK time).

A SQL Expression might work, using:

dateadd(h,X,table.datetime)

Where X is the hours difference.

Keep in mind that the datetime stored may represent a changing datetime as a result of daylight savings, etc. over time, so you may need to increase your sophistication.

A Crystal formula can also do this, as in:

dateadd("h",X,table.datetime)

A simple hardcoding would be:

{table.datetime} > datetime(year(currentdate-2),month(currentdate-2),day(currentdate-2),19,0,0)
and
{table.datetime} <= datetime(year(currentdate),month(currentdate),day(currentdate),19,0,0)

Hope this helps.

-k