datetime parameter format

Question

Post reply

datetime parameter format

mvee

Hall of Fame

Points: 3634
More actions
September 28, 2005 at 4:30 pm

#111079

Hi -
I have 4 date parameters on a report - none of which care about the time day. The field values in the report are in mm/dd/yyyy format. The parameters default to date and time. It's overwhelming and annoying that the user has to deal with the time in the parameters. I've played around with switching the date to a string using convert (varchar(10), date, 101) in the datasource and changing the parameter to string, but the filter results are not always as expected.
Any other way to do this?
Thanks!
-Megan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

yep RS handles time just like sql server does.

if time is not entered then it defaults to 12:00 AM.

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 2

Why not pre-populate the parm with values, then they just pick a date rather than having to enter one in a particular format? If you're looking at multiple years, you could always have 'cascading' parameters, where they chose a year frst, then a date within that year (ie the year selection dictates the values populated within the 'date' drop down).

Steve.

Martin N Ten Centuries Points: 1216 More actions · Answer 3

I'm assuming that when a user enters a date range like 03/10/2005 - 03/10/2005 (dd/mm/yyyy) then they just want everything for that day regardless of time...

So I'd give them two date only parameters for Start and End, the start time will be 12AM then convert the end date (also 12AM) to a char and add '23:59:59.997' to it (ie the last millisecond of the day). Convert back to datatime and bingo...

here's an example of how we do this... in dd/mm/yyyy format.

@End_Date = CONVERT (DATETIME, CONVERT (CHAR (11), @End_Date, 120) + '23:59:59.997', 120)

Hope that helps. Later.

Kindest Regards,

Martin

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 4

One thing to watch with the 'hh:mm.ss.nnn' approach, check your datatype first, if it's a smalldatetime, this will bite you because it only goes down to the minute - last week this was causing a query for me to return records from the following day because it 'rounded up'.

Steve.