In case this question is asked again, just point here: http://www.15seconds.com/faq/Data%20Access/768.htm
Thanks! > 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]
