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!

more ▼

asked Feb 07, 2012 at 11:40 AM in Default

avatar image

1.1k 56 60 66

it is nvarchar50 but i have a function that converts it to datetime...

Feb 07, 2012 at 03:12 PM jhowe

just convert it to a date and see how that changes things.

Feb 07, 2012 at 03:36 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

more ▼

answered Feb 07, 2012 at 11:54 AM

avatar image

Usman Butt
13.9k 6 12 21

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, 2012 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, 2012 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, 2012 at 02:03 PM jhowe

What is the Data Type of CreatedDateTime?

Feb 07, 2012 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, 2012 at 07:50 AM Usman Butt
(comments are locked)
10|1200 characters needed characters left

I would do it in the TSQL simply using the CONVERT function to control the data type of the column.

 USE [adventureworks]
 SET @date_time = GETDATE()
 SELECT @date_time as [Shows Date and Time values
 SELECT CONVERT(DATE, @date_time) as [Date only for SSRS]
more ▼

answered Feb 07, 2012 at 12:12 PM

avatar image

Fatherjack ♦♦
43.7k 79 97 117

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

Feb 07, 2012 at 02:07 PM eghetto

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

Feb 07, 2012 at 02:19 PM Usman Butt
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 07, 2012 at 11:40 AM

Seen: 5773 times

Last Updated: Feb 08, 2012 at 07:50 AM

Copyright 2016 Redgate Software. Privacy Policy