Passing (or converting) vb short date to sql 101

Question

I have an SSRS report that you can click on to drill down and see more details about a cell. To do this, when you click a cell it passes parameters that populate a SQL statement in another report.

One of the parameters that it passes is a short date, mm/dd/yyyy. My problem is when month or day do not have double digits. So....

Short date = 1/1/2013

SQL 101 format = 01/01/2013

When the short date is passed, it doesn't match the SQL version exactly so it doesn't find any records when it should.

This returns nothing:

where convert(char(10),dt.DATE_CREATED, 101) like '1/1/2013'

BUT, this returns all of the rows I need:

where convert(char(10),dt.DATE_CREATED, 101) like '01/01/2013'

My problem is I need to somehow do this conversion of adding these zeros within SSRS...

Answer 1

This fixed my problem: convert(char(10),dt.DATE_CREATED, 101) = Convert(datetime, @day)

Answer 2

You should be able to use the VB function DateTime.Parse

DateTime.Parse(Fields!yourValue.Value)