How to run a query for "Today's" date from 12:00am to 11:59PM
sql, sql-server, sql-server-2008
Solution
Don't use BETWEEN, use >= the start date and < a day past the end date:
WHERE (dtCreated >= @startdate AND dtCreated < DATEADD(day, 1, @enddate))
The reason is that BETWEEN will find up until 12:00am of the end date, but not past then.
UPDATED
For todays date, you can do this:
WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, dtCreated)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
This will check that it has a `dtCreated` equal to some point today.
UPDATED
As @ScottChapman has pointed out, you can do the same thing without the conversion gymnastics by casting to the `DATE` type directly. This type is only available in MSSQL 2008 and later, however.
Problem
I have a simple query that pulls a payout report for our day. I'd like to automate this to send every night, however I want the report to run for That day 12:00AM - 11:59 PM daily... I will be sending the reports at 9:00 PM, so I suppose it will only need to get until 9:00 PM if that's easier. Here is my query: ``` SELECT COUNT(*) AS Number, SUM(dblPayoutAmt) AS Amount FROM Payouts WHERE (dtCreated BETWEEN @startdate AND @enddate) ```