Between data problems...

• Dave Rooke

  SSC Eights!

  Points: 906

  More actions

  January 30, 2007 at 4:24 am

  #174584

  I am having problems trying to get a date selection to work correctly....

  I have a smalldatetime column for which i am trying to search between two dates, but i get the following problem.

  SQL part:

  AND (dbo.ISSUE_MAIN.REQUEST_DATE_TIME >= '04/01/2007') 

  AND (dbo.ISSUE_MAIN.REQUEST_DATE_TIME <= '05/01/2007')

  This only gives me records for 04/01/2007 and not inclusive of the 05/01/2007?

  I have also tried BETWEEN, which gives me the exact same results?

  If i change 05/01/2007 to 06/01/2007, then i get 04 and 05 records, but not inclusive of 06.

  Thanks in advance for help on this matter....

   

   

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  January 30, 2007 at 4:31 am

  #686399

  hi

  does the REQUEST_DATE_TIME column contain date as well as time. if so you will have to format this column in your query.

  "Keep Trying"

• Dave Rooke

  SSC Eights!

  Points: 906

  More actions

  January 30, 2007 at 4:57 am

  #686403

  Thanks for that - forgot about that problem...

  I did this and got the results correctly:

   

  AND CAST(FLOOR(CAST(dbo.ISSUE_MAIN.REQUEST_DATE_TIME AS FLOAT))AS DATETIME) >= 'From_Date'

• Sugesh Kumar

  One Orange Chip

  Points: 27311

  More actions

  January 30, 2007 at 7:26 am

  #686438

  try using the date in this format YYYYMMDD there is already a thread going in this forum with the same issue and lot of users are answering the same.

  Cheers,
  Sugeshkumar Rajendran
  SQL Server MVP
  http://sugeshkr.blogspot.com

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  January 30, 2007 at 9:09 am

  #686488

  SQL Server doesn't have DATE columns, it only has DATETIME columns. The resolution of times in SQL Server is 3 milliseconds, thus you could do something like

  AND (dbo.ISSUE_MAIN.REQUEST_DATE_TIME >= '04/01/2007')

  AND (dbo.ISSUE_MAIN.REQUEST_DATE_TIME = '04/01/2007')

  AND (dbo.ISSUE_MAIN.REQUEST_DATE_TIME < '06/01/2007')

  This is equivalent to saying that you want all numbers starting with 4 or 5 (which would include 4.1, 4.9, 5.0 and 5.1 and 5.9) rather than your original query which is all numbers between 4 and 5 (which would only include 4.1, 4.9 and 5.0). Dates are effectively a floating point value in SQL Server and in most programming languages.

  Also, as suggested, you should try to format your dates in an international format such as YYYYMMDD HH:NN:SS.ss . You can also investigate the CONVERT statement which allows you to provide a parameter indicating which datetime string format is being used (Australia/UK vs US, etc).

  If that's not clear, please let me know and I'll try to provide more examples. Cheers

• Ian Yates

  SSCoach

  Points: 19738

  More actions

  January 30, 2007 at 9:11 am

  #686490

  By the way, when I say SQL has DATETIME columns, not DATE, SQL Server interprets a given date that has no time as midnight starting on that date. So the <= '5/01/2007' is interpreted as <= '5/01/2007 00:00' and would exclude times such as '5/01/2007 10:43' because it is technically after midnight on that date. Hence the '5/01/2007 23:59.997' example, or the better 2nd example which uses < rather than <=.