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

• Alan Burstein

  SSC Guru

  Points: 61074

  More actions

  February 16, 2016 at 10:38 am

  #1859036

  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: 183587

  More actions

  February 16, 2016 at 10:49 am

  #1859040

  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: 61074

  More actions

  February 16, 2016 at 11:17 am

  #1859046

  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: 183587

  More actions

  February 16, 2016 at 11:25 am

  #1859049

  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

  February 16, 2016 at 12:32 pm

  #1859075

  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