Re: creating a time stamp field in SQL

Alan,

Glad to be of help. Please be aware that your SQL environment must be set to
*MDY. If using STRSQL, check F13, option 1. If using SQL in a RPG program,
use SET OPTION DATFMT = *MDY;

Also, the fact that you are using 6 digits fields could give you a wrong
century result. For example, I believe that '12/31/35 would map to
'2035-12-31', although it could perfectly refer to someone born in 1935...

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Tue, May 31, 2011 at 3:12 PM, Alan Shore <ashore@xxxxxxxx> wrote:

Thanks Luis
I don't have idate, but what you replied with gave me enough

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:
midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Rodriguez
Sent: Tuesday, May 31, 2011 3:06 PM
To: Midrange Systems Technical Discussion
Subject: Re: creating a time stamp field in SQL

Also, you could try:

TIMESTAMP(idate(yourdate, '*MDY'), Time('12.00.00') )

HTH,

Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries
--



On Tue, May 31, 2011 at 2:29 PM, <rob@xxxxxxxxx> wrote:

You mean like
values cast(idate(123111, '*MDY') AS TIMESTAMP) for idate:
http://www.think400.dk/downloads.htm



Rob Berendt
--
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Alan Shore <ashore@xxxxxxxx>
To: "'midrange-l@xxxxxxxxxxxx'" <midrange-l@xxxxxxxxxxxx>
Date: 05/31/2011 02:47 PM
Subject: RE: creating a time stamp field in SQL
Sent by: midrange-l-bounces@xxxxxxxxxxxx



I forgot to mention that the numeric date field is in MMDDYY format

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

From: Alan Shore
Sent: Tuesday, May 31, 2011 2:39 PM
To: 'midrange-l@xxxxxxxxxxxx'
Subject: creating a time stamp field in SQL

Afternoon everyone
I have a file (FILEA) that contains a time stamp field
Using SQL on the file, the time stamp is displayed as
2011-05-26-22.04.08.076000
I have another file (FILEB) that contains data that I want to populate
FILEA with
Here's the problem
FILEB contains a 6 digit numeric field that represents a date. I need

this

to be in the timestamp field in FILEA
My question is :- Can this be done? And how
I have tried using date in SQL, but I cannot see how to transpose that to
a timestamp
I am not concerned about the time in the timestamp, so a time of noon
would suffice

As always, all responses gratefully accepted

Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

--
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.

--
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.