Convert date from computer format to mm/dd/yy

[jazminecat]

I have a table with dates from our AS/400 in a wierd format that doesn't look like true Julian.

106001 = 1/1/06
106002 = 1/2/06
105365 = 12/31/05

so basically, the first digit is useless, the second two are the year, and the last three are the day of the year. How can I convert this in a SQL statement to return the date as mm/dd/yy? The date field that contains these computer dates is called YTDWK.

Thanks!

 

[PHV]

DateSerial(1900+YTDWK\1000,1,YTDWK Mod 1000)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jazminecat]

Hm, I"m using ADP, I don't know if that makes a difference, but I get an error:

ADO error: DateSerial is not a recognized function name.

here's my code:

SELECT Finance_Admin.JailDetail.NAME, 
    SUM(Finance_Admin.JailDetail.HOURS) AS HoursTaken, 
    Finance_Admin.JailDetail.YTAN8 AS Emp#, 
    Finance_Admin.JailDetail.YTDWK, 
    DateSerial(1900 + 'YTDWK1000', 1, 'YTDWK Mod 1000') 
    AS Expr1
FROM Finance_Admin.[2006Dates] INNER JOIN
    Finance_Admin.JailDetail ON 
    Finance_Admin.[2006Dates].MachineDate = Finance_Admin.JailDetail.YTDWK
GROUP BY Finance_Admin.JailDetail.NAME, 
    Finance_Admin.JailDetail.YTAN8, 
    Finance_Admin.JailDetail.YTDWK

thanks!

 

[PHV]

I don't think you posted in the right forum for ADP issue.

 

[jazminecat]

ok i'll try the other one. I figured it was my SQL and not ADP but I'll repost elsewhere.

 

[PHV]

I gave you a valid JetSQL formula.
It's up to you to find the SQL Server counterpart.

 

[jazminecat]

ah, ok thank you!