Re: changing date format in update statement

Oracle stores date in generalized format, you can display in many different formats . For that you can use to_char sql function. like

to_char(Date_in_database, 'MM-DD-YYYY HH24:MI:SS');

Similarly you convert the Date in character format to Date format using to Date function
To_date('12-12-1999','MM-dd-YYYY');

in your case trying to update the date field in oracle you could try some thing like this
update table1 set datepaid = to_date('12/12/1999','MM/DD/YYYY')

Since ASP variables display date in character format and their data-type is character so you need to convert it to date datatype using to_date function.

Hope it helps!

Faheem

judiphuongtu_at_yahoo.com (jt) wrote in message news:<6f38222f.0312121129.5e43e0aa_at_posting.google.com>...
> I posted this yesterday, but I am not seeing this out yet:
>
> I am having problems with updating a date field in a certain format.
> The data is stored in an Oracle database. The date is automatically
> displayed in format, "mm/dd/yyyy" on the ASP page, but it is accepted
> only in another format, "dd/mon/yy". I want to make it consistent.
> How can I force it to accept the date in format "mm/dd/yyyy"? The
> second option would be to force it to display in "dd/mon/yy". Here's
> my current update SQL statement:
>
> commUpdate.CommandText = "UPDATE table1 SET DatePaid = '" +
> varDatePaid +
> "' WHERE ID = " + varRecID
>
> thanks,
> jt
Received on Sat Dec 13 2003 - 03:47:05 CET