Datetime Parameter Default

  • mbrady5

    SSCrazy

    Points: 2835

    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

    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

    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

    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

    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

    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

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

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