If the columns are Date columns, then the formatting of the string occurs
during either retrieval, in your app, or during presentation. Date type
values are not *stored* as any particular date *format* - they are usually
stored as some type of a fractional number or theoretically/possibly two
sets of integer values. *ALL* *formatted* dates are *strings*.
Your problem is a perfect example illustrating why I always push performing
explicit conversions between subsystems (app, database/SQL, user interface)
and point out the importance of always knowing your variables' data types.
Selecting a Date column should result in a Date subtype variant (VarType = 7
(=vbDate) - OR a Null (VarType = 1 (vbNull)) in the recordset. Assigning
the recordset variable to a VBScript variable should preserve the subtype,
unless you concatenate it to a string (for example x = "" &
rs.Fields("MyCol").Value), format or convert it.
If you have properly maintained the data type (variant subtype), the problem
would be during presentation (i.e. creating the web page). If you use
implicit conversion to string type, for example by using Response.Write
vMyDateVar, the output will be formatted according to the ASP server's
regional settings. If you want a different format such as the Oracle
default DD-MON-YYYY, you would have to produce it yourself, or - if you are
selecting the date simply for presentation, you can format it as a string in
the SQL statement. In that case, beware that you should NOT perform any
type of arithmetic, sorting, etc. on it in the application (without an
explicit conversion back to a date type variable):
SELECT TO_Char(colMyDate, "DD-MON-YYYY") as MyDate, ...
HTH,
Tore.
-----Original Message-----
From: Michael Gerholdt [mailto:[EMAIL PROTECTED]]
Sent: Friday, August 30, 2002 8:44 AM
To: ActiveServerPages
Subject: Re: date problem
> <pet peeve>
> :-> Michael - you've been a bad boy - DON'T store dates as strings! :->
>
> I assume from your description that the database column is defined as a
> char(..) or varchar2(..) in the database. Take a serious look at
modifying
> the table structure to use a Date column instead. The below will help in
> creating the converted column.
Actually, they are date columns both in the original source tables as well
as in the target table.
However, in the original tables (over which I have no control) even though
it is a date column in an Oracle db, it is stored like this:
04/21/1952 and not in the customary Oracle 04-APR-1952.
When I do a select in my web app, I always see only 4/21/52, and when I save
that to the target table the result is 4/21/2052.
The original tables are a load that comes from another database originally.
I'm not sure why the sql-loader script didn't provide a conversion to
Oracle's native date format, or how it preserved this format. Maybe I can
get a peek but that's not 'mine' either.
I'd prefer to use Oracle's format meself.
Mike
---
You are currently subscribed to activeserverpages as: [EMAIL PROTECTED]
To unsubscribe send a blank email to
%%email.unsub%%
---
You are currently subscribed to activeserverpages as: [email protected]
To unsubscribe send a blank email to [EMAIL PROTECTED]
