Between date selection

Hi,

I have the following situation.

I want to create reports with a start date parameter and an end date parameter

In my query i use "where date between @.startdate and @.enddate"

i configure my parameters so i get a nice calendar when i generate the report.

the problem is when i select for example a date starting 1 april 2007 and enddate 10 april 2007,

the result in my report gives me only the data from 1 until 9 april and not until 10.

in my database the date is stored as a date time (10/04/2007 17:25:30).

Any suggestion how i can solve my problem?

Greetings

Vinnie

Hello Vinnie,

The problem is that your database stores the time with your date and when you select a date in the date picker, it doesn't have a tme, so anything after midnight gets excluded. Try this as your where clause:

where date >= @.startdate and date < dateadd(d, 1, @.enddate)

Hope this helps.

Jarret

|||

Hi Vinnie,

When I come across this I normally use a conversion in my query:

where convert(varchar(25),date,101) between @.startdate and @.enddate

The conversion eliminates the time from the database field and will pull back all rows with the date portion between the start and end dates.

Simone

|||

Do you have a date picker control on your reporting website, so that instead of the user having to manually input the date in the correct format, they are given a control to pick the date ranges?

(Like datetimepicker control in C#)

Please let me know as that is what I need to do.

|||

Yes I do. You need to change the data type of the parameter in the Report to datetime. Go to "Report" then "Report Parameters" Select the parameter from the list on the left and change the data type to DateTime. This should automatically give the date control.

Simone