question

jhowe avatar image
jhowe 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!
sql-server-2008sql-server-2008-r2ssrs
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image Fatherjack ♦♦ commented ·
just convert it to a date and see how that changes things.
1 Like 1 ·
jhowe avatar image jhowe commented ·
it is nvarchar50 but i have a function that converts it to datetime...
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered

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
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
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.
3 Likes 3 ·
jhowe avatar image jhowe commented ·
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?
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
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)
0 Likes 0 ·
jhowe avatar image jhowe commented ·
Sorry i'm a bit confused... ideally i want ssrs to pass a DATE i don't care about the time. In my stored proc which creates a query to output as a report, i've set @startdate, @enddate to DATE datatype, as again I don't care about the time...
0 Likes 0 ·
Usman Butt avatar image Usman Butt commented ·
What is the Data Type of CreatedDateTime?
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
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]
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image Usman Butt commented ·
@eghetto The OP tagged it in SQL 2008. So DATE can be used.
3 Likes 3 ·
eghetto avatar image eghetto commented ·
DATE is 2008+ only! Use "SELECT CONVERT(DATETIME, @date_time, 112) as [Date only for SSRS]"
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.