error: dateserial is not recognized function

[jazminecat]

I posted this in the Access queries and jet sql forum, but they sent me over here.

I have a table with dates from our AS/400 in a wierd format
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. The date field that contains these computer dates is called YTDWK.

Here is my SQL:

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

when i try to run it, i get an error ADO error: date serial is not a recognized function. can anyone help me with this?

thanks!

 

[cmmrfrds]

An Access project is an ADP that connects to an SQL Server database. It appears that you are making an ADO connection to an AS400 server. If this is the case, you need to send AS400 SQL syntax or ANSI SQL standard syntax. The DateSerial function is an Access function. You will need to look in the AS400 documentation for date manipulation functions or convert the number to a date by some other means. How you do it will depend on whether the field is a character or number field.
It looks like the first 3 digits are the number of years since 1900. The AS400 must have a modulus function and you should be able to use that to pull out the days in the year as your example does.
Find the AS400 starting point for the date data type. For example, in SQL Server the starting point is 1/1/1900 and Microsoft Access it is 12/31/1899. I suspect the starting point is probably 1/1/1900, but don't know. Typically a date function will take a number that is the number of days from the starting point. For example, today is 37578 days since 1900. So, if you take your field and convert it to the number of days since the starting point and feed it to a date function it should give a valid formatted date as in the example you gave.

 

[jazminecat]

No, the information is exported from the AS400 to excel and imported into adp. So we are not connection to the 400 directly. No one down there seems to know what the starting date is. I only know that the years are formatted as 105 for 2005, 106 for 2006, 103 was 203, etc. then the last three digits are the day number for that year, from 001 to 365.

If I can drop the first digit, use the second two as year, and then somehow get a date mm/dd based on the last three digits, I will be golden. But i don't know how to do that in ADP, only in Access Jet SQL.

 

[cmmrfrds]

When you say
"AS400 to excel and imported into adp".
Does this mean the excel data is going into an SQL Server table? So, really you are dealing with an sql server table?

What is the data type of the field that contains the date?

 

[jazminecat]

hi, the data type is float.

I guess it's a sql server table. The table lives in the Access Data Project, which lives on the network. Here's the scenario, data is downloaded after each payroll from the as400 into these excel tables. They are manually imported into the access data project. In order to automate this as much as possible, and therefore keep idle hands out of the data, we do not want to change any of the datatypes at the table level. So we're trying to work with it the way it comes in. Eventually we hope to automate the imports as well.

 

[cmmrfrds]

There is a Convert function in sql server that can be used to convert and format data types. Look up either online or in the sql server documentation.
Briefly.
CONVERT(data type, your field, style) this is only one of the options for convert so please look up.

example.
Convert(datetime,YTDWK,101)

What you could do is:
divide YTDWK by 1000 and add 1900
(106002 / 1000) + 1900 = 2006
Then modulo YTDWK by 1000, the modulo operator in SQL Server is the % percent.
(106002 % 1000) = 002
Then combine as needed.

This should give you a starting point.

 

[jazminecat]

Ah! Great starting point, thank you! I"ll work with it and see what i can come up with, and post back with the results. Thank you again, I really appreciate your help!