AW: SQL Date Formatting Issue

Hi,

in either way the conversion of a character date representation with a 2
digit year, will only work will only work if the job date has the same
format, in your case MM/DD/YY.

If you already have a 4 digit year, use it for conversion. If you use a
character representation, SQL can convert any of the following formats
'YYYY-MM-DD', 'MM/DD/YYYY', 'DD.MM.YYYY'. To convert a 2 digit year, you
have to use the job's date format. For me your conversion will not work,
because we are using job format *DMY.

But to make it a little easier, SQL also can convert a 14 digit character
representation of a timestamp format YYYYMMDDHHMMSS (without separators)
into a real date.
In this way I'd change the conversions as follows.
1. The numeric field:
DATE('20' concat Digits(Mundane) concat '000000').

If you are also handling with dates in the years 19xx it may be a little
trickier, because the century must be determined correctly:
DATE(Case When NumDate >= 400101 then '19' else '20' end
concat Digits(NumDate) concat '000000')

The case clause should also be used within your conversion:
DATE(Substr(Digits(NumDate), 3, 2) concat '/' concat
Substr(Digits(NumDate), 5, 2) concat '/' concat
Case When NumDate >= 400101 then '19' else '20' end concat
Substr(Digits(NumDate), 1, 2))

In the second case try the following:
DATE(SUBSTR(TRIM(SHIP_DATE)), 5, 2) CONCAT '/' CONCAT
SUBSTR(TRIM(SHIP_DATE)), 7, 2) CONCAT '/' CONCAT
SUBSTR(TRIM(SHIP_DATE)), 1, 4)) AS DATE_SHIPPED

Or
Date(Trim(CharDate) concat '000000')

BTW:
The scalar function Date will convert a character string representation of a
date or a timestamp or a character timestamp representation into a real
date.
A real date is a binary value representing the number of days since x.
A date format only makes this binary value readeable. In this way, your code
will not output the date as 'MM/DD/YYYY', it will convert the numeric date
into a real date. If you change your date format for example in STRSQL, F13,
1, DATFMT to for example *EUR, your date will be shown as DD.MM.YYYY.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Fred Horvat
Gesendet: Wednesday, 29. July 2009 21:29
An: 'Midrange Systems Technical Discussion'
Betreff: SQL Date Formatting Issue

Hello,

I am working with a query tool and am having trouble formatting a field as a
date in a SQL Select statement. Here's what is going on. I have two files
from the iSeries. One has a date field that is defined as numeric 6.0 and
in YYMMDD format. The following code will output the date as MM/DD/YYYY

DATE(SUBSTR(DIGITS(OHETDT),3,2) CONCAT '/' CONCAT
SUBSTR(DIGITS(OHETDT),5,2) CONCAT '/' CONCAT
SUBSTR(DIGITS(OHETDT),1,2)) AS ENTRY_DATE



Another file I am working with has a date field that is defined as variable
V50 as bbYYYYMMDD. The following code gives me blank output.

DATE(SUBSTR(DIGITS(SHIP_DATE),7,2) CONCAT '/' CONCAT
SUBSTR(DIGITS(SHIP_DATE),9,2) CONCAT '/' CONCAT
SUBSTR(DIGITS(SHIP_DATE),5,2)) AS DATE_SHIPPED


I believe I have the positions line up properly as the date doesn't actually
start until position 3 in the field. Are variable length fields handled
differently in SQL for what I am trying to do?






Fred Horvat
horvat@xxxxxxxx
216-426-5692