|
|
|
|
|
email this FAQ to a colleague
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near '>'.
sSQL="Select * from Subscribe Date> 08/23/2002"
A:
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:
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:
2: Application Code to SQL statement
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).
Regards,
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
