RE: creating a time stamp field in SQL

Thanks for the head up, but (hopefully) this is a one-time deal

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 5:14 PM
To: Midrange Systems Technical Discussion
Subject: 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.

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