UPDATE a year from an ISO date field using SQL

5 pts.
Tags:
AS/400 SQL
iSeries Navigator
ISO
SQL
Dear Sir/Madam, How can I update the year from a date field. For example the date file contains '2008-12-31' and I would like to use I-Series navigator's SQL to update the date field above with '2007-11-31'.But my intention for the update here is to decrease/deduct the year by 1 year and the month by 1 month as well as there could be thousands of different date which i cannot update one by one. The date field is in ISO format. Thanks and Best Regards, Kenny Ng

Software/Hardware used:
AS/400 I-Series navigator SQL

Answer Wiki

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

We need more specificity — is your field a date type?

If the date that you have is a date type then I think this will work.
If you have a date in a char or numeric field then it needs more work.

Make a test file and try this. It should update all records in MyFile reducing MyDate

Update Myfile
Set MyDate = MyDate – 1 YEAR – 1 MONTH;

It should reduce the date by 1 year and to the same date one month before .. if the date exceeds the number in the month then the last day of the month.

Phil

Discuss This Question:  

 
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

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