Re: Valid dates allowed by the operating system

From the fine SQL reference: The date formats *JUL, *MDY, *DMY and *YMD can

They are real date fields. That DSPPFM can't handle them is weird.

Paul Nelson
Cell 708-670-6978
Office 409-267-4027
nelsonp@xxxxxxxxxxxxx


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob
Berendt
Sent: Thursday, December 21, 2017 7:36 AM
To: Midrange Systems Technical Discussion
Subject: Re: Valid dates allowed by the operating system

Have you tried "real" date fields?

CREATE TABLE QTEMP/PAUL (MYDATE DATE NOT NULL WITH DEFAULT)

Table PAUL created in QTEMP.

INSERT INTO QTEMP/PAUL (MYDATE) VALUES(date('2096-12-31')) WITH NC

1 rows inserted in PAUL in QTEMP.

SELECT * FROM qtemp/paul

SELECT statement run complete.
MYDATE
2096-12-31

However, you must also ensure this is set correctly:
STRSQL
F13=Services
1. Change session attributes
Date format . . . . . . . . . *ISO

With SQL embedded into RPG you would use
set option datfmt = *iso



Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
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: "Paul Nelson" <nelsonp@xxxxxxxxxxxxx>
To: "'Midrange Systems Technical Discussion'"
<midrange-l@xxxxxxxxxxxx>
Date: 12/21/2017 08:25 AM
Subject: Valid dates allowed by the operating system
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



Gang,

I'm working on a data mining project for a client and have come across
something weird. For whatever reason, their programmers decided that
2096-12-31 was a good date to pick for a lease expiration date, and
proceeded to populate hundreds of thousands of records with that date.

The crazy thing is that SQL doesn't like those dates, but query doesn't
care. Even crazier is that DSPPFM can't handle them. I keep getting data
mapping errors.

What is the highest valid date allowed by the system? I seem to recall
that
it was 2049-12-31 at one point.

Paul Nelson
Cell 708-670-6978
Office 409-267-4027
nelsonp@xxxxxxxxxxxxx



--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD


--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
http://amzn.to/2dEadiD


--
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: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD