Question about Converting Crystal syntax into SQL or VB

I'm having an issue with a date field in Access. I have imported data
from a database and the date field is a HUGE number rather than an
actual date. I have a formula from crystal reports to convert the
number into a date, but I;m not sure how to convert the crystal report
formula into a SQL or VB formula. the formula is below.
NumberVar Yyyy ;
NumberVar Mm;
NumberVar Dd;
Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
Date (Yyyy, Mm, Dd)
I would appreciate any help!
Thanks!
JROn 3 Feb 2006 12:23:12 -0800, jennifer.rodgers@.fidessa.com wrote:

>I'm having an issue with a date field in Access. I have imported data
>from a database and the date field is a HUGE number rather than an
>actual date. I have a formula from crystal reports to convert the
>number into a date, but I;m not sure how to convert the crystal report
>formula into a SQL or VB formula. the formula is below.
>
>NumberVar Yyyy ;
>NumberVar Mm;
>NumberVar Dd;
>Yyyy := Truncate ({CALL_HDW.CALLDAT_HDW} / 65536) ;
>Mm := Truncate (({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536)) / 256);
>Dd := Truncate ({CALL_HDW.CALLDAT_HDW} - (Yyyy * 65536) - (Mm * 256)) ;
>Date (Yyyy, Mm, Dd)
>I would appreciate any help!
>Thanks!
>JR
Hi JR,
Try if this works for you:
SELECT DATEADD(year,
(CALLDAT_HDW / 65536) - 2000,
DATEADD(month,
(CALLDAT_HDW % 65536) / 256,
DATEADD(day,
CALLDAT_HDW % 256,
'19991231')))
Hugo Kornelis, SQL Server MVP