Filtering DATETIME Stamp by Todays Date

Posted on 2006-04-12
Last Modified: 2008-02-01
We are using SQL Reporting as reporting tool for us, and I am having an issue with one report.  It goes something like this.
Created Date is 04/11/2006 05:51:39 PM  I am trying to filter by the Month/Day/Year only it does not seem to work for me.

Can some help.  Oh, I tried changing the format to "d" which displays the 4/11/2006 but still does not work.  Any help would be great.

Question by:danorme
    1 Comment
    LVL 142

    Accepted Solution

    when filtering for datetime, you have to filter with >= and < , because otherwise it will only match the exact date PLUS time.

    something like:
    Created_Date >= convert(datetime, '2006-04-11', 120) and Created_Date < convert(datetime, '2006-04-12', 120)

    resp, for today:
    Created_Date >= convert(datetime, convert(varchar(10), getdate(),120), 120) and Created_Date < convert(datetime, convert(varchar(10), dateadd(day, 1, getdate()),120), 120)

    resp, for yesterday:
    Created_Date >= convert(datetime, convert(varchar(10), dateadd(day, -1,getdate()),120), 120) and Created_Date < convert(datetime, convert(varchar(10), getdate(),120), 120)


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now