SQL Server Performance

Filtering a query with today's date

Discussion in 'Getting Started' started by davem, Aug 28, 2007.

  1. davem New Member

    I'm new to SQL, but have lot's of Access experience. I'm starting to convert my queries to SQL server, and my first major hurdle is a query that requires a filter on the date. The data for 'today' should return about 30 rows. When I enter getdate() as the criteria for the date field, no rows are returned.
    What is not making sense is if I filter the date field using '8/27/07' I get all the rows returned. If I use BETWEEN '8/1/07' AND '8/30/07' I get all the rows for the month. If I create a field using getdate() as the source aliased as testdate, I get today's date for that column. But, anytime I try to use the result of getdate() as criteria, I get no fields whatsoever.
    I have checked the table and verified the date field is datetime. I am sure this is a simple one, but the 2 books I have don't address it, and I haven't found an answer on the internet yet..
    Thanks, Dave
  2. Madhivanan Moderator

    Note that Getdate() returns both date and time. You need to omit time part
    Where datecol>=Dateadd(day,datediff(day,0,getdate()),0)
    and datecol< Dateadd(day,datediff(day,0,getdate()),1)
  3. Madhivanan Moderator

  4. davem New Member

    Madhivanan, thank you for your reply. I have noted the time part of the getdate(), but I am still not able to get the query to filter correctly.
    If I use the dateadd and datediff commands as listed above, I still get zero rows. If I use one half of the statement, such as the >= or the < section, I get the dates for that range, but when I put them together, I still get no rows. I've tried different variations of # of days for the datediff and dateadd and cannot get the query to filter if I have more than one criteria on the date.
    I'm using Microsoft SQL Server 8.0
    Thanks, Dave
  5. ndinakar Member

    If you post some sample data that actually fall in the range and are not getting selected with the query, we can help.
  6. Adriaan New Member

    Do the dates on your date column have the time of day, or do they all have midnight?
    If they're all on midnight, and you're looking for rows with today's date, you can simply use equality for the criteria:
    WHERE datecol = DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
    If the date column does have the time of day, use this:
    WHERE datecol >= DATEADD(day,DATEDIFF(day,0,GETDATE()),0) AND datecol < DATEADD(day,DATEDIFF(day,0,GETDATE()),1)
    Note the pair of >= and < operators.
    You could use DATEADD and DATEDIFF on your date column to take out the time of day, but this is likely to slow down the query. This is because SQL cannot use indexes to resolve your criteria, and probably has to do a full table scan to read the date from each row.
    If you're looking for a date range, adjust the date expressions after the >= and < operators. If you're spelling out the date, use either the 'mm/dd/yyyy' or the 'yyyy-mm-dd' format. Do not forget to add the single quotes when you spell out a date.
    Frank's article about datetime will tell you pretty much everything you need to know.
  7. davem New Member

    Thanks for all the feedback and help. I still am not having luck with the dateadd/datediff method, but I did find a method that worked.
    I used convert(varchar(10),getdate(),101) to convert the current date to a date only format, and it filters using that just fine.
    I'll be going back later and trying the other methods so I understand them.
    Thanks again, Dave
  8. Adriaan New Member

Share This Page