x

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

jhowe gravatar image

jhowe
1.1k 52 57 61

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

Usman Butt gravatar image

Usman Butt
13.9k 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, 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]

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]
more ▼

answered Feb 07, 2012 at 12:12 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1842
x588
x555

asked: Feb 07, 2012 at 11:40 AM

Seen: 2979 times

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