Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    17

    Unanswered: Convert field but retain format

    Hi,

    My problem is as follows :

    On my database I have a view that is currently casting a date as char(10). The reason for this so that i can see the dates in SQL server (0001/01/01). Now what seems to be happening, is that on the cast, my date format is getting messed around, ie : from yyyy-mm-dd i am now only getting yy-mm-dd in the text field.

    Using SQL server, i could overcome this problem by replacing the CAST with a CONVERT, but I don't know how to do this in DB2.

    Any ideas?

  2. #2
    Join Date
    Sep 2003
    Posts
    17
    In other words, how do i cast "0001/01/01" (date) as "0001/01/01" (char) ?
    Currently after casting the yyyy becomes yy ...

  3. #3
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276
    use the function VARCHAR_FORMAT

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Instead of CAST try using the CHAR function

    CHAR(HIRE_DATE, ISO)

    The ISO date is in format 'YYYY-MM-DD'

    If you want all dates and times to appear in the ISO format without using the CHAR function, I believe that there is a DB2 instance setting to accomplish this.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Actually, I am not sure which format you wanted. Here are the DB2 formats:

    ISO yyyy-mm-dd 1991-10-27
    USA mm/dd/yyyy 10/27/1991
    EUR dd.mm.yyyy 27.10.1991
    JIS yyyy-mm-dd 1991-10-27

  6. #6
    Join Date
    Sep 2003
    Posts
    17

    Smile Voila!

    Thanks guys, worked brilliantly. Also saved me alot of headline headache.

Posting Permissions

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