asked
SSRS pass date instead of date/time
Hello in SSRS when the user is choosing a date from the calendar, how do I get SSRS to drop the time part? I'm concerned about passing 2012-02-07 00:00:00 as when using a between function I will lose any records with a stamp > 2012-02-07 00:00:00... any ideas would be greatly appreciated!
You can handle it in your TSQL statement. Change your Starting date parameter always pointing to start of the chosen date i.e. 12 A.M and your ending date parameter pointing to 11:59:59.997 P.M. There are many possible ways like
SET @StartingDate = CAST(REPLACE(CONVERT(VARCHAR(25), @StartingDate , 111),'/','') AS DATETIME) -- POINT TO 12 A.M.
SET @StartingDate = CAST(CONVERT(VARCHAR(25), @StartingDate , 112) AS DATETIME) -- POINT TO 12 A.M.
But the most suitable solution could be
SET @StartingDate = DATEADD(DAY, DATEDIFF(DAY, 0, @StartingDate), 0) --POINT TO 12 A.M
SET @EndDate = DATEADD(ms, -3, DATEADD(DAY, DATEDIFF(DAY, -1, @EndDate) , 0))--POINT TO 11:59:59.997 P.M i.e. 23:59:59.997
If you want to keep the DATE datatype for the params, then change your WHERE clause filter to something like this CreatedDateTime >= @DateStart AND CreatedDateTime < DATEADD(DAY, 1, @DateEnd) This will return all the data for given date range.
Yes usman i've seen something like this before so if my parameters are declared as DATE, I can use AND (CreatedDateTime) = DATEADD(DAY, DATEDIFF(DAY, 0, @DateStart), 0) AND (CreatedDateTime) = DATEADD(ms, -3, DATEADD(DAY, DATEDIFF(DAY, -1, @DateEnd) , 0))) In my where clause?
If the params are of Date DATATYPE, the time portion is irrelevant. You should have declared your parameters with the same data type as of your "CreatedDatetime" field. In this case, only @DateEnd needs to be handled carefully. Either you change @DateEnd data type to DATETIME2, DATETIME (whatever the field's datatype is) OR create a local parameter and assign @DateEnd's value to it. Then apply the DATEADD logic on that. (This is against the best practice to assign the parameter value to local variable)
I would do it in the TSQL simply using the CONVERT function to control the data type of the column. USE [adventureworks] GO DECLARE @date_time DATETIME SET @date_time = GETDATE() SELECT @date_time as [Shows Date and Time values SELECT CONVERT(DATE, @date_time) as [Date only for SSRS]
5 People are following this question.
