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

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.

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.

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>).

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.

Would you be so kind as to point out which of my statements you consider to be incorrect. Thank you.

Try this

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

is it possible to output the date without the hour minutes and seconds, just the YYYY-MM-DD alone?

use date function

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?

Check the manual for the valid date format string.

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

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

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:If the column DATUM contains a date in the format YYYY-MM-DD, like '2006-11-13', you can use What if the column DATUM doesn't contain a date in the format YYYY-MM-DD, like '13.11.2006'?
I tried 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.


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).

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