How to run a query for "Today's" date from 12:00am to 11:59PM

Question

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)

Answer 1

SET @StartDate = CAST(GETDATE() AS date)
SET @EndDate = DATEADD(MINUTE, -1, DATEADD(DAY, 1, @StartDate))

SELECT        COUNT(*) AS Number, SUM(dblPayoutAmt) AS Amount
FROM            Payouts
WHERE        (dtCreated BETWEEN @startdate AND @enddate)

Answer 2

Some of these answers are close, but exclude times in the final minute of the day, like 11:59:30 PM. This query will include all of today:

SELECT        COUNT(*) AS Number, SUM(dblPayoutAmt) AS Amount
FROM            Payouts
WHERE        (dtCreated >= CAST(GETDATE() as date) AND dtCreated < DATEADD(day, 1, CAST(GETDATE() as date)))

Note that this won't work in SQL Server 2005 or below, as the date type was added in SQL Server 2008.

Answer 3

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.