Datetime Parameter Default

Question

Post reply

Datetime Parameter Default

mbrady5

SSCrazy

Points: 2835
More actions
February 16, 2016 at 6:29 am

#304786

I am looking to set-up a datetime default parameter. The field in SQL is Datetime and I'd like to default the start date to
@start = Yesterday at 6 PM and @end = today at 6 AM.
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61141 More actions · Answer 1

I prefer to use T-SQL for this type of thing. The way I would do this is:

1) Create a Dataset named something like DS_DateDefaults (use whatever matches your naming convention)

2) Use this query for DS_DateDefaults:

SELECT

StartTime = DATEADD(HH,6,CAST(CAST(GETDATE()-1 AS date) AS datetime)), -- 6AM yesterday

EndTime = DATEADD(HH,6,CAST(CAST(GETDATE() AS date) AS datetime)); -- 6AM today

3) Set the default for each parameter (@start and @end) to point to the DS_DateDefaults dataset. Use StartTime for @start, EndTime for @end.

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

For this particular case, there's a simpler solution using common date routines[/url]

SELECT

StartTime = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), -.25), -- 6PM yesterday

EndTime = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), .25); -- 6AM today

--To be able to use other time frames

SELECT

StartTime = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) - 1, '18:00'), -- 6PM yesterday

EndTime = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), '06:00'); -- 6AM today

Alan, you missed the PM part. That's an easy fix, though.

EDIT: Added code with more flexibility.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Luis Cazares (2/16/2016)
For this particular case, there's a simpler solution using common date routines[/url]
SELECT
StartTime = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), -.25), -- 6PM yesterday
EndTime = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), .25); -- 6AM today
--To be able to use other time frames
SELECT
StartTime = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) - 1, '18:00'), -- 6PM yesterday
EndTime = DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), '06:00'); -- 6AM today
Alan, you missed the PM part. That's an easy fix, though.
EDIT: Added code with more flexibility.

Good catch. I misread the AM/PM and I assumed there was a better way to do the dates. 🙂

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Just another option.

SELECT

StartTime = DATEADD(HH,-6,CAST(CAST(GETDATE() AS date) AS datetime)), -- 6PM yesterday

EndTime = DATEADD(HH,6,CAST(CAST(GETDATE() AS date) AS datetime)); -- 6AM today

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

mbrady5 SSCrazy Points: 2835 More actions · Answer 5

Hi Guys,

thanks for the great solution. I guess if it were a perfect world, I'd be able to change the SSRS date/Time params to customize a time period.

Your solutions both met my request. Thanks