Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2004
    Posts
    24

    TO_DATE Function

    Does anyone know how to covert a string to a date using the TO_DATE scalar function? I have read the DB2 documentation but no example have been given.


    This doesn't work:

    select TO_DATE('12-06-2005','MM-DD-YYYY) from sysibm.sysdummy1


    I want to be able to use this to dictate what format the data is returned in.

    the column in my table is VARHCAR. I want to bring back the date in a format I tell it.

    Does anyone know how to utilize the TO_DATE scalar function and has examples?

    Thanks,

    Beck

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,429
    DATE doesn't have a "format" - it's just a number. To format a date you'll need to convert it to a string of a specified format.

  3. #3
    Join Date
    Mar 2004
    Posts
    24
    You didnt understand. The data is VARCHAR, I want to return a date.

    Data looks like this: 12-06-2005, but it is VARCHAR.


    I want to return a date when querying, using the TO_DATE scalar function, so I can tell it what date format to return.


    I can use the DATE function and it works, but I want to use TO_DATE. Does anyone know how and have examples.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,429
    No, _you_ didn't understand. A DATE doesn't have a "format", and you can't "tell it what date format to return". By using the TO_DATE function you are converting a string into a group of 8 packed decimal digits.

    When you want to represent a DATE in a human-readable form you need to convert it into a string while specifying the format you want.

    To format a DATE into a string use CHAR(<date>,<format>).

  5. #5
    Join Date
    Mar 2004
    Posts
    24
    Once again, you are wrong. You most definitely can tell a string what date format to return. You can do it in Oracle w/ the TO_DATE function. DB2 has the same function. I am looking for examples. You can mask a VARCHAR to return a date.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,429
    Quote Originally Posted by beckdb2
    Once again, you are wrong.
    Would you be so kind as to point out which of my statements you consider to be incorrect. Thank you.

  7. #7
    Join Date
    Aug 2003
    Posts
    106
    Try this

    select TO_DATE('2005-12-06 00:00:00','YYYY-MM-DD HH24:MIS') from sysibm.sysdummy1

    You are the creator of your own destiny!

  8. #8
    Join Date
    Sep 2005
    Posts
    24
    is it possible to output the date without the hour minutes and seconds, just the YYYY-MM-DD alone?

  9. #9
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    use date function

  10. #10
    Join Date
    Sep 2005
    Posts
    24
    I am trying to insert an integer and a TIMESTAMP in DB2:

    insert into datum (ID,DATE) VALUES(1,TO_DATE('20061110','YYYYMMDD'))

    Following error:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0171N The data type, length or value of argument "2" of routine
    "SYSIBM.TO_DATE" is incorrect. SQLSTATE=42815

    what is wrong?

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,429
    Check the manual for the valid date format string.

  12. #12
    Join Date
    Jul 2005
    Location
    Irvine, CA
    Posts
    23
    Quote Originally Posted by klaus1
    I am trying to insert an integer and a TIMESTAMP in DB2:

    insert into datum (ID,DATE) VALUES(1,TO_DATE('20061110','YYYYMMDD'))

    Following error:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0171N The data type, length or value of argument "2" of routine
    "SYSIBM.TO_DATE" is incorrect. SQLSTATE=42815

    what is wrong?

    Try: insert into datum (ID,DATE) VALUES(1,'2006-11-10 00:00:00');

    If you have a timestamp, you need to give DB2 a timestamp formatted value. This one says midnight, 11-10-2006. You can also add in the seconds and miliseconds, but you don't have to. I avoid timestamps in my designs when a simple date will suffice.

    -- Steve

  13. #13
    Join Date
    Sep 2005
    Posts
    24
    thanks, this works.
    my only question in DB2 is now:
    How can I convert a char* datum="13.11.2006" to a timestamp format, so that I can insert it into my column datum which represents a timestamp format.
    I use a UDF function which returns a timestamp. I can use it from my clp prompt as follows:
    db2 => VALUES myOra.TO_DATE('13.11.2006','mm-dd-YYYY')

    1
    --------------------------
    2006-11-13-00.00.00.000000

    1 record(s) selected.

    the prototyp of the function:
    CREATE FUNCTION MyOra.TO_DATE (inCH Varchar(50), inFMT Varchar(50))

    I use a CLI/ODB Connection to insert as follows:
    "INSERT INTO DATUM (ID,DATUM) VALUES (?, myOra.TO_DATE(CAST(? AS VARCHAR(10)))";

    getting the error:
    SQLCODE = -99999, SQLSTATE = 22008
    MESSAGE: [unixODBC][IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format was detected; that is, an invalid string representation or value was specified. SQLSTATE=22007

    Has anyone an idea?
    thanks a lot,
    Klaus
    Last edited by klaus1; 11-13-06 at 08:15.

  14. #14
    Join Date
    Nov 2004
    Posts
    1,377
    TO_DATE is a synonym for TIMESTAMP_FORMAT (you can find examples there).

    DB2 understands only strings like '2000-01-01 09:00:00' as valid TIMESTAMPs.
    The only valid TS format string is:
    Code:
    'YYYY-MM-DD HH24:MI:SS'
    If the column DATUM contains a date in the format YYYY-MM-DD, like '2006-11-13', you can use
    Code:
    insert into MYTABLE (ID, TIJDSTIP) VALUES(1, DATUM || ' 00:00:00');
    What if the column DATUM doesn't contain a date in the format YYYY-MM-DD, like '13.11.2006'?
    I tried
    Code:
    select TIMESTAMP_FORMAT('31-12-1999 23:59:59', 'DD-MM-YYYY HH24:MI:SS') 
    from sysibm.sysdummy1;
    but that didn't work. DB2 only verifies if the second parameter is equal to "The-one-and-only-valid-TS-format-string", if not it raises an error. This is not a DB2 bug but a feature .

    Sadly, you will have to go trough the loops and convert (using SUBSTR and " || '-' ") the date string in the format 'DD.MM.YYYY' to the only 'YYYY-MM-DD ...'-format DB2 recognises at this moment. It shouldn't be that way, but it is.


    Once again, you are wrong.
    On the other side: please be a little more friendly towards the people who are trying to help you. I've read and reread everything you two wrote, and n_i was right.
    DB2 normally doesn't get you frustrated (in this case it did frustrated me, so I understand you), but when it does, don't work it out on the people on this forum who try to help.


    So now all is said, I think you'd better use DATE instead of TO_DATE as the deliverd functionality of TO_DATE is not in par with its promisses (perhaps in DB2 V 10?) and all it does is add compexity (you have to append 00:00:00 to your DATUM).
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  15. #15
    Join Date
    Sep 2005
    Posts
    24
    Hi!

    Thanks for your reply.
    I tried to use the insert statement as follows:
    char *DATUM="2006-11-11 00:00:00";

    "INSERT INTO DATUM (ID,DATUM) VALUES (?, TIMESTAMP_FORMAT(?,'YYYY-MM-DD HH24:MIS')";

    using the SQL_C_TYPE_TIMESTAMP for the SQLBINDPARAMETER( )


    Error code:
    SQLCODE = -99999, SQLSTATE = 22008
    MESSAGE: [unixODBC][IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format was detected; that is, an invalid string representation or value was specified. SQLSTATE=22007

    Sorry for not beeing friendly, but I can't exactly find the paragraph I wrote in an unfriendly way. Sorry, my english is not the best, hopefully it is not unfriendly too! :-)

    Maybe you can help me once more?
    Thanks in advance!
    Klaus

Posting Permissions

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