RE: SQL Date Formatting Issue

Ok I will take a look. The UL has a typo. It is
http://www.think400.dk/downloads.htm the 's' was missing.


Thanks

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Campin
Sent: Wednesday, July 29, 2009 4:05 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Date Formatting Issue

If you go to www.think400.dk/download.htm and look for iDate, there is all
the code, objects and documenation for a User Defined Function called iDate.

Select iDate(YMDField,'*YMD') From YourFile

This output a date data type that you can do anything you want

Select iDate(Trim(YMDFieldWithBlank,'*YMD') from your file

Does the same thing.

The only catch is if you are querying from outside the AS/400 you need to
qualifiy the iDate command for it to find it.

If iDate is in UTILLIB you would need to do

Select UTILLIB.iDate(YMDField,'*YMD') from yourfile



On Wed, Jul 29, 2009 at 1:45 PM, Brian Johnson
<brian.johnson.mn@xxxxxxxxx>wrote:

On Wed, Jul 29, 2009 at 2:28 PM, Fred Horvat <horvat@xxxxxxxx> wrote:

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

DIGITS built-in function converts number to character. But you are
feeding it a character column.

--
Brian Johnson
brian.johnson.mn@xxxxxxxxx
--
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.