SSRS pass date instead of date/time

Question

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!

Answer 1

0

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

answered Feb 07 '12 at 11:54 AM

Usman Butt
13.8k ● 6 ● 8 ● 14

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?

Feb 07 '12 at 12:47 PM jhowe

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)

Feb 07 '12 at 01:36 PM Usman Butt

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...

Feb 07 '12 at 02:03 PM jhowe

What is the Data Type of CreatedDateTime?

Feb 07 '12 at 02:18 PM Usman Butt

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.

Feb 08 '12 at 07:50 AM Usman Butt

(comments are locked)

Answer 2

0

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]

answered Feb 07 '12 at 12:12 PM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

DATE is 2008+ only! Use "SELECT CONVERT(DATETIME, @date_time, 112) as [Date only for SSRS]"

Feb 07 '12 at 02:07 PM eghetto

@eghetto The OP tagged it in SQL 2008. So DATE can be used.

Feb 07 '12 at 02:19 PM Usman Butt

(comments are locked)

SSRS pass date instead of date/time

Follow this question

Related Questions