I know this is a little late, but another post just made this meaningful
anyway...
Three problems:
1: A WHERE clause would help... This is probably the cause for the syntax
error.
2: "Date" is a Reserved Word in SQL Server. Enclose in [], or that may
result in another syntax error.
3: Once you get the first two fixed, your query will return the wrong
result... read on:
<pet peeve>
You must understand what TYPE of data you deal with - especially with dates
- and even more especially when you communicate it between type-different
subsystems such as a user and your application and your application to a
database via SQL statements.
Understand the difference between a "date" and a "date string". Typically,
a "date" typed variable will be stored internally as a floating point
number, with the whole number representing the date (typically an offset
from some date determined by be 0), and the fraction representing the time
of day. A "date string" is just a translation of the date variable to a
meaningful format that we can read.
You also need to understand how your expressions are being evaluated,
including in a SQL statement. A numeric expression (one that starts with a
number or a numeric function and includes numbers and arithmetic operators
and numeric functions) will be calculated according to arithmetic rules:
08/23/2002 is a numeric expression, and will be calculated as 8 / 23, and
the result divided by 2002. SQL Server may consider the expression an
integer expression, in which case you'll get Midnight, January 1, 1900,
otherwise it will add 15 seconds and 10 milliseconds for the fraction, i.e.
00:00:15.010 AM on January 1, 1900.
Different systems (and subsystems) format and interpret date strings
differently. This can be based on the development environment and coding
language, the functions used, a subsystem's configuration (read the SQL
Server/Database configuration settings) as well as regional settings on the
various computers involved (client PC, application PC/web server, database
server, etc.), their respective subsystems, and even specific settings for a
particular web page.
Because there are so many environmental factors involved, it is risky to
make assumptions about date formats. You should perform explicit conversion
between date variables and their string representation (and back),
explicitly specifying what format to use, and always store/manipulate date
values in their "native" (internal storage format in a date type variable)
form.
Specifically, for SQL Server queries from VBScript, create custom conversion
functions to produce and interpret a well defined date string format from
your VBScript (in VB, you can use the Format$() function with a format
specification, but that is not available in VBScript). Then you tell SQL
Server in no uncertain terms what it is you are sending it in the SQL
Statement using the CONVERT function with a suitable/matching Style
parameter.
Date Conversion flow:
1: User Input to Application code
Unless a date control producing a date type value (i.e. not a string
value) or a known and/or explicitly specified (non-regional) date string
format, make sure you receive the individual date parts separately. Then
use:
dtMyDateVar = DateSerial(iYear, iMonth, iDay) + TimeSerial(iHr,
iMin, iSec)
to produce the typed variable in your app.
2: Application Code to SQL statement
sSQL = sSQL & " WHERE MyDate = CONVERT(DateTime, '"
& Year(dtX) & "-" & Month(dtX) & "-" & Day(dtX) & " "
& Hour(dtX) & ":" & Minute(dtX) & ":" & Second(dtX)
& "', 120)"
Of course, I usually have a custom function to convert the date to the
desired format. The above example is slightly cheating, since it uses
implicit conversion of the integer values returned by the Year() etc.
functions. Anyway, I have found that the SQL Server Style 120 (or 121 if
you need milliseconds) is easy to read, and difficult to misinterpret.
3: From the database to your application, the resultset would return a
properly typed variable - just be on the lookout for potential Null
values...
4: From the application to the user, you would usually use a format
consistent with your input format. Of course there are tricks that you can
employ in order to determine the regional format on the client PC, and
massage input and output accordingly. Hint: Convert a date that can be
interpreted in any order (such as 01/02/03), and see what you get. Of
course, the day, month and year separator may not be the same... (for
example dd/mm-yyyy).
</pet peeve>
Regards,
Tore.
-----Original Message-----
From: M. H. K. [mailto:[EMAIL PROTECTED]]
Sent: Thursday, September 26, 2002 3:22 PM
To: ActiveServerPages
Subject: Date filtering on SQL
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '>'.
sSQL="Select * from Subscribe Date > 08/23/2002"
I'd appreciate it if someone could point out what the problem is here ?
---
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]
