AW: SQL Date Formatting Issue

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

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