Subtracting dates

[madsstiig]

Hi.
I'm sitting with a report where I have to determine the number of certain events happening less than 48 hours from the execution of an exam (we're in an x-ray department).
The 2 fields that should be subtracted are:
{MAP_EVENT_AUDIT.DATE_TIME_CREATED} in the format x seconds since 01-01-1970 and
{DOSR_STUDY.STUDY_DATE} in an ordinary YYYYMMDD format.
I know how to translate {MAP_EVENT_AUDIT.DATE_TIME_CREATED} into YYYYMMDD HH:MM:SS but that is where I ran my head against the wall.
Can you please help?
Best regards Mads, State Hospital in Denmark.

 

[madsstiig]

... oh and I forgot
This is happening in Crystal Reports XI, btw - sorry ...

 

[Madawc]

The DateDiff includes options to compare dates and times, including differences in hours. Use HELP within Crystal to get the details.

I'd suggest first displaying the result, to confimr it is correct before using it for anything else.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[MCuthill]

madststiig:

"to determine the number of certain events happening less than 48 hours from the execution of an exam"

The function Madawc suggested will provide the difference between two dates, which could be used for this purpose, I prefer to use the DateAdd() function to create a "bound" versus looking for elements where a difference is less than (or greater than) a certain value -- nearly certain either will work, so it is just a matter of preference.

All of a sudden, I realize I am unsure which of your date fields is which... so I will do this generically (my apologies) and you will need to sub in the appropriate fields.

If you are looking to create a bound 48 hours after the execution of an exam, and report all items between the exam execution and said time:
{@BOUND_48HPostExam}

DateAdd("h",48,{Table.ExamExecutionDT})

Your selection criteria would then have a line such as follows contained somewhere within:

{table.EventDateTime} <= {@BOUND_48HPostExam}

If not a report criteria, the BOUND formula can be used for triggers in condition counts or other places should that better fit your needs (perhaps your report lists all events within 96 hours, but you want to count only those within 48 hours).

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."