Filtering a report on a DateTM field using a parameter if possible?

I am currently writing a report that will use as one of the filters the date (in this case it is the date of service of a physician office visit).

however the DB developers included the date and the time of the visit in one field so my resulting data set contains 4/13/2007 4:30pm for example, so using an @date parameter as a filter on the data field doesn't work because when you run the report and enter the date in the parameter it doesn't return anything.

I am an SQL Report writer amateur so I am sure there is an easy fix, any and all help will be appreciated.

Mark

Hello Mark,

You will need to use the convert function with a style included. This should do what you need, put this in the where clause of your SQL query :

where...

and convert(varchar, DateField, 101) = convert(varchar, @DateParameter, 101)

The convert function with 101 as the style will remove the time from your field (and parameter) and display like this: mm/dd/yy

Hope this helps.

Jarret

Multi-values datetime parameters are possible. The only issue is that we cannot use expressions based on that parameter in Filter tab (under "Values") but it has to be just Parameters!DateParameter.Value (with In operator)

Shyam

Jarret,

Thanks for taking the time to reply, your response pointed me in the right direction. Here's the actual code that did what I needed it to do...

SELECT ..., CONVERT(varchar, Schedule.App_DtTm, 101) AS DATE

HAVING (CONVERT(varchar, Schedule.App_DtTm, 101) = @DATE)

In the SELECT statement SQL creates a column for the CONVERT function as DATE which is then filterable by the parameter

@DATE

Thanks again.

Mark.