March 10, 2009 at 7:26 am
Hi,
I work in a Juvenile Court with a detention facility. I need to build reports that have start date, start time, and end date, end time parameters.
I have figured out that I need to use a string parameter for each of these and then combine the date and time strings and convert to a single DateTime.
My problem is that I have not been able to come up with a string format that users can enter simply/ reliably and that will then convert.
Any suggestions would be MOST appreciated as this is a pressing problem!
Thanks,
Sandi
March 10, 2009 at 7:38 am
This won't qualify as a great answer but what you really want is a .Net date time picker for start and end date times. You can customize the reporting services we service in 2005 and 2000 to add this if you are a programmer but this is time consuming. I have no idea with 2008 as I have not used it yet.
The quick but not great answer (and I really need more sleep if I thought that was a phrase):
1. Create a data set with 24 hourly entries, the value member should be what the SQL date time wants
2. Put 4 parameters in the main query concatenating the date and times
3. Allow users to type the dates as these are a pain in a combo.
4. Make the uses pick from the times from combo so you should generally get a valid date time.
There are several issues with this but it is quick to implement.
March 10, 2009 at 10:56 am
My suggestion would be this... I'm assuming your users will only be given the choice of H/M. Much of this depends on how specific you need your time entered... If it's hours/minutes, fine, otherwise you may want to look into something else or use this as an example that you can expand on.
Use a datetime for the Startdate and EndDate.
For the Time portions, you could either do a 24Hr dropdown box(datetype int with 24 values) or a 12 HR dropdown, with AM/PM as another drop down. You could add a minutes column depending on how precise a time you want your users to be allowed to select, or you could do it in 5 or 15 minute increments if that's all they need. Again this all depends on your data. I know that drop down lists can get a bit long for this type of information, but it's a really easy way to validate the time they put in is less than 60.
So now you have your date (startDate), you have a parameter for hours(startHours) and one for minutes(startMinutes).
Now it's easy to make sure it will convert to the appropriate datetime to pass into your query. In your query just add
Where MyDateColumn >= Dateadd(m,@startMinutes,dateadd(h,@startHours,startdate) )
You can do the same for the enddate /hours/minutes. You don't have to worry about arithmetic errors with a concatenation and you don't have to worry about differing dateformats as you are dealing with just a date and adding to it.
-Luke.
Edited for clarification in first sentence...
March 10, 2009 at 10:59 am
March 10, 2009 at 11:17 am
Thanks for the (very quick) suggestions!
I think Luke's answer provides the key - using dateadd. That one never occurred to me, and converting the time was giving me fits!
We are converting from a Cobol database to SQL server and Crystal Reports to SSRS. I can't begin to tell you how helpful this forum has been! First time I've posted, but I look every day and use much of what I read.
Sandi
March 10, 2009 at 11:22 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply