September 27, 2010 at 1:59 pm
I am trying to set up my parameters so they look like the following:
Invoice Date between start date and end date
Start date and End Date are my parameters. Is there a way to do this? Thanks in advance for any assistance.
September 27, 2010 at 2:15 pm
Your dataset query for the invoice data must include start and end parameters. Something like
SELECT *
FROM invoice
WHERE invoice.date BETWEEN @Start AND @End
These should automatically link to like named report parameters. If you want to give the parameters default values, I usually add the defaults to the Report Parameters on the Default Values tab.
September 28, 2010 at 7:14 am
Daniel Bowlin (9/27/2010)
Your dataset query for the invoice data must include start and end parameters. Something like
SELECT *
FROM invoice
WHERE invoice.date BETWEEN @Start AND @End
These should automatically link to like named report parameters. If you want to give the parameters default values, I usually add the defaults to the Report Parameters on the Default Values tab.
Just a small warning about using the between statement like this. The datetime you get from the datetime picker in SSRS only gives you the date, not a time as in '2010-09-28 00:00:00.000'. So if you store full datetimes in your invoice.date you'll be missing any records where the time is after midnight such as '2010-09-28 08:15:00.000'. There are two ways to address this. IF you want to continue to use between make certain that either a) you column doesn't contain time portions and leave it as it, b) use something like DateAdd(ms, 86399997, @End) to get you to the last ms of the day for today it would evaluate to '2010-09-28 23:59:59.997' or c) use the following...
WHERE invoice.date >= @Start AND invoice.date < dateadd(dd,1,@End)
-Luke.
September 28, 2010 at 7:28 am
Thanks. I am going to see if this works.
September 29, 2010 at 6:59 am
some random thoughts use date diff.
Thanks & Regards,
Veeren.
Ignore this if you feel i am Wrong. 😉
September 29, 2010 at 8:27 am
October 12, 2010 at 3:20 am
That's what I do (in some cases, for some specific reasons, that escape me right now):
WHERE InvoiceDate >= DATEADD(d, DATEDIFF(d, 0, @StartDate), 0)
AND InvoiceDate < DATEADD(d, DATEDIFF(d, 0, @EndDate), 1)
October 12, 2010 at 8:58 am
drowlfs (10/12/2010)
That's what I do (in some cases, for some specific reasons, that escape me right now):WHERE InvoiceDate >= DATEADD(d, DATEDIFF(d, 0, @StartDate), 0)
AND InvoiceDate < DATEADD(d, DATEDIFF(d, 0, @EndDate), 1)
Yup, the datediff/dateadd functions don't help you specify the between or GTE and LT functions, which is what the OP was asking about. What they do in the code you posted is remove the time portion of the date, or more specifically, set the time to 00:00:00.000 (midnight of the date passed to the functions) by calculating the difference (datediff) in days from 0 (aka 1900-01-01 00:00:00.000) and the date in your parameter, then with that number, you add that number (using DateAdd) to 0 as above or 1(1900-01-02 00:00:00.000) to add the total number of days back.
Your code ensures that you get all the records that occur between midnight and 23:59:59:997 based on the start and End Dates.
-Luke.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply