If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > TO_DATE Function

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,399
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.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,399
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>).
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,399
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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!
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jul 2006
Location: Pune , India
Posts: 433
use date function
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #11 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,399
Check the manual for the valid date format string.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
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.


Quote:
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
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
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
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On