Date and Time

[Cbucci]

I am putting together a query using Linked tables from our EHR Database...I have the following code that can be used within Crystal Reports:
NumberVar ID := {document.clinicaldate};
NumberVar Sec := Truncate (ID/1000000);
//Convert seconds to days

NumberVar Days := Truncate(Sec / 3600 / 24);


// Convert days to a date and time
DateTime(Date(1960,01,01) + Days

, Time(0,0,0) + Sec)

However I do not know how to do this in Access so that when I am putting together a query I can use this to alter the existing data that comes in for the field document.clinicaldate.

An example of what comes in now is: 1472049502000000

Any help would be great thanks.

Chris

[NTC]

what is 1472049502000000 ?

a stand alone date? seconds from another date? unclear....

[Cbucci]

what is 1472049502000000 ?

a stand alone date? seconds from another date? unclear....

The clinical date of the document - the first ten digits (counting from left to right) are a time/date stamp in an
encoded format representing the number of seconds since January 1, 1960.

[NTC]

well that's interesting.....so you'll want to research the dateadd function....

trim that value field to the relevant digits that's a string function...

might have to cast to integer next...not sure...

then add those seconds to that fixed starting date - - and should equal the correct date.

as I type this I remember that dates are fundamentally added values to a core starting point but that has always been behind the scene in the OS itself - have never seen it upfront in the user's hands.....

[Cbucci]

well that's interesting.....so you'll want to research the dateadd function....

trim that value field to the relevant digits that's a string function...

might have to cast to integer next...not sure...

then add those seconds to that fixed starting date - - and should equal the correct date.

as I type this I remember that dates are fundamentally added values to a core starting point but that has always been behind the scene in the OS itself - have never seen it upfront in the user's hands.....

I figured out the sql to write:
to_char(to_date('01-01-1960','MM/DD/YYYY') +
( trunc(d.clinicaldate/1000000) / (60 * 60 * 24) ),'MM/DD/YYYY')
"Date"
where d.clinicaldate is pulling in the clinicaldate value from the documents table and truncating it down to just see the seconds.

Now I just need to figure out if Access has anything that can do this so that user's could simply put a date criteria in and have access run their report instead of me.