Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2013
    Posts
    15

    Unanswered: Converting a Numeric value to a Date

    How would I convert values like 20130723 'CCYYMMDD' in a Numeric (8,0) field so it shows like 7/23/2013 in the field.

    I have tried many things and still can't get it.

    I thought something like: CONVERT(CORDATE, CAST(CORDATE AS VARCHAR(8)), 112) would work but nope. Any help would be appreciated. Thanks!

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Cast it as varchar(8), then substr the year, month, and day, and necessary dashes/slashes).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I use this:

    DATE(TIMESTAMP_FORMAT(CAST(CORDATE AS VARCHAR(8)) , 'YYYYMMDD'))
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Do you want to convert to DATE datatype or a string with format 'MM/DD/YYYY'?

    If you want to convert to DATE datatype, Florin's expression would be an answer.

    If you want to convert to a string with format 'MM/DD/YYYY', Mrcus's suggestion would be an answer.
    Or, try
    TRANSLATE('ef/gh/abcd' , CAST(20130723 AS VARCHAR(8)) , 'abcdefgh')

  5. #5
    Join Date
    Jun 2013
    Posts
    15
    I tried Florin's example and I believe that's the one I needed to get my final output to be in the correct format. Thanks so much Florin! I appeciate the other suggestions and sample too. I'm going to try out tonkuma's code sample as well in another area. Thank you for helping me with this. I really appreciate the help this forum provides.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you do it that way, you may be subject to different results based on the territory code of the database server or the client. That may, or may not, be OK in your situation.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Jun 2013
    Posts
    15
    Quote Originally Posted by Marcus_A View Post
    If you do it that way, you may be subject to different results based on the territory code of the database server or the client. That may, or may not, be OK in your situation.
    That makes sense Marcus, not sure if it would be a concern yet, but it might. Could you provide with an example of how to write it so I can try it out? Thanks Marcus.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in North America dates are mm/dd/yyyy
    in europe dates are (generally) dd?mm?yyyy but the separator can be one of many symbols
    / - \ and so on.
    best practicse is to use ISO date format yyyy/mm/dd which is the SQL standard
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Actually, the standard ISO 8601 specifies dashes ("-") as the separators for the date components, so it would be YYYY-MM-DD, not YYYY/MM/DD.
    ---
    "It does not work" is not a valid problem statement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •