Problem converting numeric to date data type

435 pts.
Tags:
DB2/400
SQL
I have a zoned decimal field as s 6 and i am trying to convert it to date format as mmddyy in sql...It giving error ..please help me out ..I tried using date and cast function but its not working !!

Software/Hardware used:
as400 V6R1

Answer Wiki

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

Discuss This Question: 6  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
    I have a zoned decimal field as s 6 You have the field where? In a file? As a program variable? and i am trying to convert it to date format as mmddyy in sql... Is this a direct SQL query? Or is it embedded SQL? (If embedded, what is the programming language?) Tom
    125,585 pointsBadges:
    report
  • deepu9321
    It Should work if you are using Date function. What is the Error Message you are getting exactly?? Mention Programming language, that will be better for understanding. Pradeep.
    3,870 pointsBadges:
    report
  • munduuu
    Its a field of a PF and i am running it through SQL query....
    435 pointsBadges:
    report
  • philpl1jb
    http://www.itjungle.com/mgo/mgo050102-story02.html Shows how to convert zoned to SQL date DATE( SUBSTR(CHAR(19000000 + YMD),1,4) ||’-‘|| SUBSTR(CHAR(19000000 + YMD),5,2) ||’-‘|| SUBSTR(CHAR(19000000 + YMD),7,2) ) If your SQL session format is *USA and the date separator is a forward slash (/), you could also convert the date this way: DATE( SUBSTR(CHAR(19000000 + YMD),5,2) ||’/‘|| SUBSTR(CHAR(19000000 + YMD),7,2) ||’/‘|| SUBSTR(CHAR(19000000 + YMD),1,4) ) Here are a few examples lifted from this site 1 convert the date to char parse it into date format and use date function or 2. use math to get the parsed date sections and then convert them to char either way the DATE function processes a char string. Phil
    50,505 pointsBadges:
    report
  • DoneThat
    And don't forget to make sure you have a valid date in the field to begin with. If not, SQL (such as the sample code provided by Philpl1jb) will barf. Gary
    830 pointsBadges:
    report
  • TomLiotta
    CHAR(19000000 + YMD) Also be aware that this is looking for 6-digit numbers in the form YYMMDD. If your date values are stored as MMDDYY or DDMMYY, you'll need to reformat them accordingly. Also note that adding 19000000 to a 6-digit YYMMDD numeric date value will give you a year in the 20th century prior to 2000. You'll need to decide if/when to use 20000000 or 19000000. You might be best served by creating a UDF() that does the work. Tom
    125,585 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