SSRS date

[ielamrani]

Hi,

I created a report in ssrs with 2 parameters. Startdate and Enddate

I want to default startdate to show today' date starting at Midnight and enddate to default to now.

I am able to get Enddate to work but not Startdate

thanks in advance.

 

[pbaldy]

How about

DATEADD(dd, 0, DATEDIFF(dd, 0, GetDate()))

 

[ielamrani]

I tried it and I am getting this error:

.....dd is not declared

thank you.

 

[pbaldy]

Hmm, worked in SSMS for me. Try

DATEADD(day, 0, DATEDIFF(day, 0, GetDate()))

 

[Minty]

I would agree about the Dateadd(Day... part;

-- Today including time info  : getdate()
-- Beginning of today without time info    : DATEADD(DAY, DATEDIFF(day, 0, getdate()), 0)
-- Tomorrow without time info : DATEADD(DAY, DATEDIFF(day, 0, getdate()), 1)
-- Beginning of current month : DATEADD(month, datediff(month, 0, getdate()), 0)
-- Beginning of last month    : DATEADD(month, datediff(month, 0, getdate())-1, 0)

--CONVERT(VARCHAR(19),GETDATE())		: Nov 04 2014 11:45 PM
--CONVERT(VARCHAR(10),GETDATE(),10)		: 11-04-14
--CONVERT(VARCHAR(10),GETDATE(),110)	: 11-04-2014
--CONVERT(VARCHAR(11),GETDATE(),6)		: 04 Nov 14
--CONVERT(VARCHAR(11),GETDATE(),106)	: 04 Nov 2014
--CONVERT(VARCHAR(24),GETDATE(),113)	: 04 Nov 2014 11:45:34:243

 

[ielamrani]

this is what I did:

under startdate exp I put: =DATEADD(day, 0, DATEDIFF(day, 0, GetDate()))
under enddate exp I put: =now()

dataset criteria: WHERE (b.hospital_name_location LIKE 'lh') AND (a.triage_arrival_time LIKE ?) AND (a.triage_arrival_time LIKE ?)

i am still getting an error

 

[pbaldy]

Where exactly is this? I haven't worked with SSRS, but I can't imagine Now() is valid there. You appear to be mixing Access functions (Now) with SQL Server functions (GetDate).

 

[ielamrani]

this is in ssrs and yes now() gives today' date and current time.

I put in startdate: =DateAdd(DateInterval.Second, +1, DateAdd(DateInterval.Day, 0, Today()))

and I get 8/11/2016 12:00:01 AM

under enddate: now()

and I get : 8/11/2016 1:09:52 PM

which is exactly what I want. but I am getting this error

"the fractional part of the provided time value overlows the scale...., increase bscale DBPARA....."

 

[pbaldy]

I'll yield to someone with SSRS experience then. What I posted worked fine in SSMS, and I've used it in stored procedures and functions. In other words SQL like:

SELECT...
FROM...
WHERE DateTimeField Between DATEADD(day, 0, DATEDIFF(day, 0, GetDate())) And GetDate()

 

[ielamrani]

thank you for your help anyway.

 

[ielamrani]

Issue resolved. I was missing one change in the query and now it's working.

I put the following:

startdate: =DateAdd(DateInterval.Second, +1, DateAdd(DateInterval.Day, 0, Today()))

Result: 8/12/2016 12:00:01 AM

Enddate:
=cdate(format(now, "MMM dd, yyyy HH:mm:ss"))

Result: 8/12/2016 11:00:49 AM

query: (a.triage_arrival_time >= ?) AND (a.triage_arrival_time >= ?)

Thank you all for your help.

 

[pbaldy]

Thanks for updating us with the solution.