Forum OpenACS Q&A: Handling dates (oracle 10g)

I'm having problems with the date format. I don't know if I'm doing something wrong, or if this is an oracle10g related problem.

I have a date input (I'm using ad_form), I convert the date to the format YYYY-MM-DD HH24:MI:SS and I send it as a parameter to my tcl create function, but the part of the date "HH24:MI:SS" is just ignored. When I see the log, after setting the date to the YYYY-MM-DD HH24:MI:SS format, I just see YYYY-MM-DD 00:00:00, no matter what time(hh24:mi:ss) I set.

When debugging this, I noticed that if I do the insert/update directly to the database (something like: insert into my_table values (...,:my_date_in_YYYY-MM-DD_HH24_MI_SS_format,..) - without using the tcl wrapper function, or updating the date after creating the item), then the date is stored correctly. But I think this is not the way to do it.

Am I doing something wrong?

Thanks,

jopez

If the Tcl create function you are passing the date to is your own and you control it's inputs, then passing in the date as a string will work if you use to_date() (or to_timestamp if you are inserting into a timestamp column) to specify the format when doing the insert into the database. But if you are using or writing a function that needs to be generic, this is a harder problem to solve.

What I ended up doing with my use of the CR was creating my new object with the date set to an empty string, and then inserting the real value into the database so I could use to_date().