The difference is in how it is kept (think: stored) - not how it is
represented. When *representing* a *date* in an *email* - the
*representation* is a string. But there is a big difference between saving
the *string representation* in a variable, and that of saving the *date
value* in a variable.
In VBScript, to get a date value, you have to either use a date function to
construct the date value, or convert the value from a string or other
representation.
For String to Date conversion:
** Best:
Separate values for Year, Month, Day, Hour, Minute, Second allows the safest
conversion.
Use
DateValue(CInt(sYear), CInt(sMonth), CInt(sDay))
+ TimeValue(CInt(sHour), CInt(sMin), CInt(sSec))
This approach is completely isolated from any locale, regional setting, or
other concerns and gives the developer full control of the conversion.
** Acceptable:
Make sure that the string is in the (a) default format as determined by your
locale and regional settings.
Use
CDate(sStandardDateString)
This is dependent on locale and regional settings, and requires that (a)
standard format is used. While accepting different formats, it will fail
between US and European (& others?) systems/date formats.
** Will work for certain things:
Use a date string format that starts with the 4-digit year, followed by
2-digit month, 2-digit day, 2-digit hour (NO AM/PM), 2-digit minute and
2-digit second - with or without delimiters. These strings will at least
sort and compare correctly, but may not convert directly to date values, and
mixed type expressions would mostly fail.
** Chancy:
VB will perform implicit conversions in some cases. While this can save
some marginal typing, it is not safe, and in VBScript these implicit
conversions primarily occur with built in functions and COM objects. This
is not to be trusted.
For Date to String conversions:
** Best:
Format the date string explicitly. Unfortunately, VBSCript does not include
the Format() function - only regional/locale dependent conversion functions.
Consider writing your own date formatting routine. When interfacing with a
database, use the Convert() function (for SQL Server, TO_Date for Oracle,
etc.) on an explicitly formatted date string with an appropriate Style or
format descriptor.
VBScript:
sSQL = sSQL & " And MyDateCol > Convert(DateTime, '" &
MyFormatDateFunc(dtDateVar, "yyyy-mm-dd hh:nn:ss") & "', 120)"
Acceptable:
Use the built-in FormatDate() functions - but test with a date that
illustrates the output - especially Year/Month/Day sequence and number of
positions. (Manipulate the string if you need to supply a date string in a
different format.) The primary reason that this is acceptable, is that
VBScript does not provide the capability to natively format a date to an
explicit format.
Chancy: Pretty much anything else...
HTH,
Tore.
-----Original Message-----
From: Ken Schaefer [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 31, 2002 11:04 PM
To: ActiveServerPages
Subject: Re: SQL Query Question
<somthing to ponder />
What datatype is 2002-08-01, and how is it different to a string?
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Scott" <[EMAIL PROTECTED]>
Subject: Re: SQL Query Question
: If there's one thing I've learnt on this list it's to deal with
: dates as 2002-08-01 and not treat them as strings...
:
:
: ----- Original Message -----
: From: "Nathan Steiner" <[EMAIL PROTECTED]>
: To: "ActiveServerPages" <[EMAIL PROTECTED]>
: Sent: Thursday, August 01, 2002 5:49 AM
: Subject: RE: SQL Query Question
:
:
: : <giddy mode="on">
: : I Love Tore's <pet peeve/>'s. They have helped me a lot over the last
year
: : and a half.
: : </giddy>
: :
: : -ns
: :
: : -----Original Message-----
: : From: Bostrup, Tore [mailto:[EMAIL PROTECTED]]
: : Sent: Wednesday, July 31, 2002 2:37 PM
: : To: ActiveServerPages
: : Subject: RE: SQL Query Question
: :
: :
: : <pet peeve>
: : I assume the column signupdate is a Date/Time column and the database is
SQL
: : Server:
: :
: : Never compare a date with a string!
: :
: : Try
: : Select *
: : from customer
: : where signupdate >= CONVERT(DateTime, '2002-07-30 00:00:00', 120)
: : and signupdate <= CONVERT(DateTime, '2002-07-30 23:59:59', 120)
: :
: : </pet peeve>
---
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]
