• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 372
  • Last Modified:

Filtering DATETIME Stamp by Todays Date

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.

1 Solution
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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)


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now