Strange date in AS400

[ermagarden]

We have a client who has old versions of AS400...

A date field has dates like "21,906"... I'm told this means :Feb 19, 2006 (2 is for feb. 1,9=19. 06=2006)

Now I wanted to write a sql to get rows using that date and subtract it from today's date...

But how I can compare that date to today's date?? how I make it into YYYY-MM-DD ??

[arrow483]

Re: Strange date in AS400

This isn't such a strange date - its simply a numeric MDY field. In RPG this is easily handled. In SQL. however, you probably need to reformat it. Most of the time SQL returns the current date in *ISO format, but depending on your job description, it can be different.

I had a problem when dealing with CYYMMDD fields in Mapics, I created this function. Change the substrings a little, and you should be able to reformat yous MDY field.

The date range check was added due to some bogus dates in our data.

-- Converts a CYMD to ISO char string
--    that can be used as input to a DATE function
--  Example   DATE(F_ISO(cymdDate))
--
CREATE FUNCTION PgmLib/F_ISO(Cymd DEC(7,0))
   RETURNS Char(10)
   LANGUAGE SQL
   DETERMINISTIC

BEGIN
RETURN(
Case
  When Cymd > 9991231 Then
   '9999-12-31'
  When Cymd = 0       Then
   '1940-01-01'
  When Cymd < 1000101 Then
'19' || Substring(Char(Cymd),1,2)
     || '-' || Substring(Char(Cymd),3,2)
     || '-' || Substring(Char(Cymd),5,2)
  Else
'20' || Substring(Char(Cymd),2,2)
     || '-' || Substring(Char(Cymd),4,2)
     || '-' || Substring(Char(Cymd),6,2)
End
 );
END