Consulting

Results 1 to 7 of 7

Thread: Datetime Parameter with date of Today or Today-1 and time set at 7am

  1. #1

    Datetime Parameter with date of Today or Today-1 and time set at 7am

    Hello,

    I am trying to set a default report parameter in SQL Server 2008.
    I need to set the default start date to yesterday at 6am and the default end date to today at 5:59am.

    Any help would be greatly appreciated, I have been looking this up on Google but have not found the correct answer.

    Thank You

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    StartDate = Format(Now - 1, "MMM, dd,yyyy") & " 6:00AM"
    EndDate = Format(Now, "MMM, dd,yyyy") & " 5:59AM"
    Put the cursor in any word and press F1 for help on that function

    Show us two samples of a valid start date and of a valid end date, (one before the 10th and one after,) and we can adjust the Format Arguments for you.

    You do realize that you are going to miss one minute's action everyday?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Thank you very much for your help.
    Yes, I do realize there is a minute missing.
    It will be alright though because it is not a constantly running process, the heats are about every hour and a half.
    I use >= and <= in the query to catch everything.

  4. #4
    Hello All,

    I am back. I have included what was requested in addition to a problem I have encountered when I tried to use the information from above.

    Example of start and end dates:

    start date 2015-07-06 11:33:01.000 end date 2015-07-06 11:41:12.000
    start date 2015-06-20 19:27:42.000 end date 2015-06-20 19:36:28.000

    When I use the = Format(Now - 1, "MMM, dd,yyyy") & " 6:00AM" for the start date I get "operator '-' is not defined for type 'Date'"
    and when I use = Format(Now, "MMM, dd,yyyy") & " 5:59AM" for the end date I get "end date doesn't have the expected type".

    I have both at Date/Time types.

    Thank you for your help.

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    We need to change the formatting, and with 3 decimal places, we only need to skip 1 millisecond's activity
    Dim strStartDate As String
    Dim strEndDate As String
    strStartDate = Format(Now - 1, "yyyy-mm-dd 6:00:00.001")
    strEndDate = Format(Now - 1, "yyyy-mm-dd 6:00:00.000")
    Then use the variables in your code instead of the Format function

    The truth is, Microsoft stores dates as 15 decimal place numbers, so even if you did use 6AM for both Times, the odds are greatly against reporting the same activity twice.

    You might also try
    Dim dteStartDate As Date
    Dim dteEndDate As Date
    dteStartDate = DateAdd("d", -.75, Date)
    dteEndDate = DateAdd("d", .25, Date)
    If you use Now to return a Date, you also get the 15 decimal place time portion, but Date only returns the Integer portion.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    I am not sure what I am doing wrong.

    When I add the variables and then use them in the parameter value I get an error message that states "Variable values can not be used in a report parameter expression."

  7. #7
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    We are starting to get out of my league, but I suspect that is due to the Expression construction. Can you post that process here?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •