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

[bmichelle510]

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

[SamT]

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?

[bmichelle510]

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.

[bmichelle510]

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.

[SamT]

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.

[bmichelle510]

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."

[SamT]

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?