January 22, 2009 at 5:06 pm
Hi,
I currently have a report that is constrained by start and end dates. These dates are fed from a calendar in my parameters section. The date is in datetime format which includes hr:mm:ss which I don't want to include because it selects the time at which I ran the report. I want it to run from 00:00:00 to 23:59:59.
Anyone know how I can do this?
January 22, 2009 at 5:24 pm
You can't modify the calendar control, so - you modify the parameter in the SQL statement. The general formula is to zero out the time portion of the date parameter. For example, with two date parameters as @startDate and @endDate, the SQL statement would be:
SET @startDate = dateadd(day, datediff(day, 0, @startDate), 0); -- date with 00:00:00.000 time
SET @endDate = dateadd(day, datediff(day, 0, @endDate + 1), 0); -- next day with 00:00:00.000 time
And, we use that in our query as:
WHERE datecolumn >= @startDate -- include all dates greater than or equal to our start date
AND datecolumn < @endDate -- include all dates less than (but not equal to) our end date
If the user selects a start date of 2008-12-01 and an end date of 2008-12-31, our parameters would end up as:
@startDate = '2008-12-01 00:00:00.000'
@endDate = '2009-01-01 00:00:00.000'
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply