need to subtract from CurrentDateTime 1

[reitter]

I'm trying to finish my select statement for a Crystal10 report. The users want the data based on 4:01pm the previous day to 4:00pm on the current date. We will set the report to run daily at 4:30pm. The field in question is a DateTime field.

I'm drawing a blank on how to make this work.

 

[MisterMo]

is the statement going in the selection formula or in a command query?

Mo

 

[reitter]

it is in the select statement.

 

[MisterMo]

this may seem a bit long but it does the job

where table.date between convert(datetime, convert(varchar,datepart(YYYY,getdate())) + '-' + convert(varchar,datepart(MM,getdate())) + '-' + convert(varchar,datepart(DD,getdate()-1)) + ' 16:01:00') and
convert(datetime, convert(varchar,datepart(YYYY,getdate())) + '-' + convert(varchar,datepart(MM,getdate())) + '-' + convert(varchar,datepart(DD,getdate())) + ' 16:00:00')

this way is dynamic and every day it will change automatically

Mo

 

[reitter]

Ok, I've substituted my tablename {vResultsforCitation.ClinicalDTTM} in plast of 'table.date' pasted this in my formula and I keep getting errors atthe 'between convert' text.

What am I not doing correctly?

 

[kskid]

Try this

{table.datetime} in [datetime(date(currentdate - 1),time(16,01,00)) to datetime(date(currentdate),time(16,00,59)]

-lw

 

[reitter]

That works!!

Thanks so much this is what I was trying to accomplish but I was going about it all wrong.

 

[MisterMo]

my soultion would never have worked in the selection formula option because is part of a select statement

just a missunderstanding

Mo