changing date format in update statement

[jt]

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.Comm andText = "UPDATE table1 SET DatePaid = '" +
varDatePaid +
"' WHERE ID = " + varRecID

thanks,
jt

[FaheemRao]

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@ya hoo.com (jt) wrote in message news:<6f38222f. 0312121129.5e43 e0aa@posting.go ogle.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.Comm andText = "UPDATE table1 SET DatePaid = '" +
varDatePaid +
"' WHERE ID = " + varRecID
>
thanks,
jt