[thelist] SQL : Comparing string date with a date column

[Rudy_Limeback at maritimelife.ca]

hi oskar

nobody else jumped on this, so here goes...

yes, it sounds like sql/server is treating your dates as strings

    SELECT * From lvdata 
   WHERE RDate >= '10.10.2000'
     AND Rdate <= '21.11.2000'

in sql/server, dates are stored as two 4-byte integers -- one for the 
number of days before or after the base date of january 1, 1900, and one 
for the number of milliseconds after midnight (dates prior to the base 
date are stored as negative values)

therefore sql/server will always have to do a conversion of *any* date 
format in your query

here is some documentation that i have from sql/server 6 (i think) --

   ____________________________________________
  |
  |    2) numeric format
  | 
  |    specify month, day, and year in a string with 
  |    slashes (/), hyphens (-), or periods (.) as separators
  | 
  |    this string must appear in the following form:
  | 
  |    <num> <sep> <num> <sep> <num> [<time_spec>] [<time_spec>]
  | 
  |    examples of acceptable numeric formats:
  | 
  |       [0]4/15/[19]96 (mdy)
  |       [0]4-15-[19]96 (mdy)
  |       [0]4.15.[19]96 (mdy)
  |       [04]/[19]96/15 (myd)
  | 
  |       15/[0]4/[19]96 (dmy)
  |       15/[19]96/[0]4 (dym)
  |       [19]96/15/[0]4 (ydm)
  |       [19]96/[04]/15 (ymd)
  | 
  |    when the language is set to us_english, the default 
  |    order for the date is mdy
  |
  |    you can change the date order with the SET DATEFORMAT
  |    statement, which determines how date values are 
  |    interpreted
  |
  |    if the order doesn't match the setting, the values 
  |    are not interpreted as dates (because they are 
  |    out of range), or the values are misinterpreted
  |
  |    for example, 12/10/08 can be interpreted as one of six 
  |    dates, depending on the DATEFORMAT setting
  |
  |____________________________________________


one thing you might do is to always specify your dates in ISO format -- i 
have used ISO format in many different database systems and have never had 
any problems with it 

    SELECT * From lvdata 
   WHERE RDate >= '2000-10-10'
     AND Rdate <= '2000-11-21'

<tip>
ISO format is explained in detail here --
  A Summary of the International Standard Date and Time Notation
  http://www.cl.cam.ac.uk/~mgk25/iso-time.html
</tip>

if ISO format doesn't immediately solve your problem, double-check your 
server and see what your DATEFORMAT is -- you may have to get your 
sql/server sysadmin to help you out on this (dontcha just love microsoft)


rudy.ca