RE: SQL - Cast Numeric to Date

Thanks for the help Loyd and Elvis...it's working.

- Michael


> -------- Original Message --------
> Subject: RE: SQL - Cast Numeric to Date
> From: "Goodbar, Loyd (ETS - Water Valley)" <LGoodbar@xxxxxxxxxxxxxx>
> Date: Mon, December 05, 2005 12:04 pm
> To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> 
> This works by creating an *ISO formatted date (2005-12-05):
> 
> select date(substr(char(20051205),1,4) concat '-' 
>        concat substr(char(20051205),5,2) concat '-' 
>        concat substr(char(20051205),7,2)) 
> from sysibm/sysdummy1 
> 
> Per the SQL reference, the only numeric conversions are allowed as the
> number of days since 1/1/0001.
> 
> HTH,
> Loyd
> 
> Loyd Goodbar
> Senior programmer/analyst
> BorgWarner
> E/TS Water Valley
> 662-473-5713
> 
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
> michael@xxxxxxxxxxxxxxxxxx
> Sent: Monday, December 05, 2005 10:49
> To: Midrange Systems Technical Discussion
> Subject: SQL - Cast Numeric to Date
> 
> I have an 8.0 field in a file. I want to do something like this:
> 
> Select Date(Num_Field) as Mydate...
> 
> I can see how to do it with a CAST on a character field - what do I need
> to do for a numeric field in CCYYMMDD format? It's not really ISO...at
> least I can't see how to specify no seperators.
> 
> Thanks...
> 
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.