How to convert separate date and time parameters to a valid datetime?

Question

I have a report with date and time (only an hour and minutes) parameters which are separate. The Date parameter is of type Date/Time, and the Time parameter is set as Text. I want to merge those two values into one because I want to pass a minimum number of arguments to the stored procedure. I tried to achieve that goal in many ways but SSRS returns an error for every attempt.

If I try to use expression like this:

=Format(FormatDateTime(Parameters!startDate.Value, DateFormat.ShortDate).ToString() + Parameters!startTime.Value, "dd/MM/yyyy HH:mm")

SSRS returns this error:

Error conterting data type nvarchar to datetime.

And when I tried to use Datetime.Parse like this:

=DateTime.Parse(Format(FormatDateTime(Parameters!startDate.Value, DateFormat.ShortDate).ToString() + Parameters!startTime.Value, "dd/MM/yyyy HH:mm"))

SSRS said:

The Value expression for the query parameter '@startDate' contains an error: The string was not recognized as a valid DateTime. There is a unknown word starting at index 0.

When I removed the FormatDateTime function I get yet another error:

The Value expression for the query parameter '@startDate' contains an error: Input string was not in a correct format.

Do you have any ideas how to write this expression correctly?

PS. I use SSRS 2008 R2.

Answer 1

This works for me:

=CDate(Format(Parameters!Date.Value, "yyyy-MM-dd") + " " + Parameters!Time.Value)

Didn't try and troubleshoot your specific examples, but they may be running into issues where you're not including the space between the date and time.

The above expression may be suitable for your report; don't know robust it would be in different locales.

You could also consider doing the concatenation/conversion in custom code if you need more flexibility.