Converting date values in AS/400 database

30 pts.
Tags:
AS/400 database
AS/400 Query
How do I convert a date value 20/09/1231 for 31st Dec 2009 in the database to be able to use a query of checking this date against current(date)?

Software/Hardware used:
AS400 Query
ASKED: November 26, 2009  10:10 AM
UPDATED: November 30, 2009  9:59 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

I want to ensure I understand your situation.

a column, which is a DATE data type, contains the date for September 9, 1231.
that is MONTH=09 & DAY=20 & YEAR=1231
and you want MONTH=12 & DAY=31 & YEAR=2009.

It appears you have confused the USA format with the European format.
USA format is mm/dd/yyyy
EUR format is dd.mm.yyyy

I prefer the ISO format which is yyyy-mm-dd

Using the ISO format the following will change a DATE data type
from 1231-09-20
to 2009-12-31

By the way, this is from DB2 on z/OS.

CREATE TABLE TTEST (DATE_COL DATE);
INSERT INTO TTEST VALUES(’20.09.1231′);
SELECT DATE_COL FROM TTEST; –1231-09-20

SELECT MONTH(DATE_COL) , DAY(DATE_COL) , YEAR(DATE_COL) FROM TTEST;

SELECT SUBSTR(CHAR(DATE_COL),1,2) FROM TTEST;
SELECT SUBSTR(CHAR(DATE_COL),3,2) FROM TTEST;
SELECT SUBSTR(CHAR(DATE_COL),6,2) FROM TTEST;
SELECT SUBSTR(CHAR(DATE_COL),9,2) FROM TTEST;

UPDATE TTEST
SET DATE_COL =
SUBSTR(CHAR(DATE_COL),9,2) ||
SUBSTR(CHAR(DATE_COL),6,2) || ‘-’ ||
SUBSTR(CHAR(DATE_COL),1,2) || ‘-’ ||
SUBSTR(CHAR(DATE_COL),3,2) ;
SELECT DATE_COL FROM TTEST; –2009-12-31
SELECT MONTH(DATE_COL) , DAY(DATE_COL) , YEAR(DATE_COL) FROM TTEST;

DROP TABLE TTEST;

Hope this helps.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    ...a date value 20/09/1231 for 31st Dec 2009 in the database... Since that' (20/09/1231) is a fairly unlikely date, I'd suspect that it actually is 2009-12-31 and that you're simply applying a MM/DD/YYYY or DD/MM/YYYY edit to arrive at the date that you showed. Most likely, you simply need to handle the date with a proper definition. What is the field definition in the database? (What data type? Length?) Tom
    125,585 pointsBadges:
    report
  • Teandy
    Why would you want to change the date format in the database? If this is an actual date data type in the file you can use the CHAR operator, or one of the other operators, in Query/400 to convert the date to the format you want. If this is a date that is in error in the file, then you need to write some sort of program to display the date to a screen so it can be corrected and written back to the file.
    5,860 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following