SSRS date (1 Viewer)

ielamrani

Registered User.
Local time
Yesterday, 21:59
Joined
May 6, 2008
Messages
51
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

Wino Moderator
Staff member
Local time
Yesterday, 18:59
Joined
Aug 30, 2003
Messages
35,741
How about

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

ielamrani

Registered User.
Local time
Yesterday, 21:59
Joined
May 6, 2008
Messages
51
I tried it and I am getting this error:

.....dd is not declared

thank you.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 18:59
Joined
Aug 30, 2003
Messages
35,741
Hmm, worked in SSMS for me. Try

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

Minty

AWF VIP
Local time
Today, 02:59
Joined
Jul 26, 2013
Messages
9,369
I would agree about the Dateadd(Day... part;
Code:
-- 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

Registered User.
Local time
Yesterday, 21:59
Joined
May 6, 2008
Messages
51
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

Wino Moderator
Staff member
Local time
Yesterday, 18:59
Joined
Aug 30, 2003
Messages
35,741
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

Registered User.
Local time
Yesterday, 21:59
Joined
May 6, 2008
Messages
51
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

Wino Moderator
Staff member
Local time
Yesterday, 18:59
Joined
Aug 30, 2003
Messages
35,741
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

Registered User.
Local time
Yesterday, 21:59
Joined
May 6, 2008
Messages
51
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

Wino Moderator
Staff member
Local time
Yesterday, 18:59
Joined
Aug 30, 2003
Messages
35,741
Thanks for updating us with the solution.
 

Users who are viewing this thread

Top Bottom