write half date then select date from date picker which display in DateTimeOffset instead of Date format

Question

I am using SSRS 2008 R2. I have problem in Date Parameter whose type is Date/Time and it is like date picker.

When we write half date and go to select date from date picker then date is displayed in DateTimeOffset type which stops execution of my report.

->error: Conversion from type 'DateTimeOffset' to type 'String or Date' is not valid.

Steps to get issue (Date is displayed in DateTimeOffset instead of DateTime(Normal)) :
1) Create Date parameter with Date/Time type.

2) Run report so Date Parameter display as Date Picker control.

3) Write half date in Date parameter like 1/1.

4) Now without moving elsewhere select Date picker symbol and select any date.

5) It will display date in type of DateTimeOffset(1/1/2012 12:00:00 AM +05:30) instead of Date(1/1/2012).

If Anyone have found this issue and got solution then Please send me solution.
If need more detail let me know.

Thanks in Advance.

---

Vir14

Answer 1

Hi Vir14,

I am able to reproduce the scenario in my test environment. Besides, if I input "1/5" in the parameter prompt box and select any date value from the calendar control, only the Month part of the "1/5" (i.e. "1") is kept in the datetimeoffset value generated automatically. Moreover, if the month part is larger than 12, the datetimeoffset will be generated randomly. To avoid the error caused by the datetime parameter, we can use "FormatDateTime(Parameters!Date.Value.DateTime,2)" to replace "Parameters!Date.Value" in the expressions that reference the parameter. In this way, no matter the user inputs half of the date manually or selects the date from the calendar control directly, the parameter pass to the report is the short date.

Reference:
Data Types in Expressions (Report Builder and SSRS)

If you have any questions, please feel free to ask.

Regards,

---

Mike Yin
TechNet Community Support

Answer 2

Thank you Mike Yin,

Please can you tell me the root cause of behavior for displaying DateTimeOffset format.

Can we control the format of Parameter displaying value mean  Date(1/1/2012) format instead of DateTimeOffset(1/1/2012 12:00:00 AM +05:30) ?

If need more detail let me know.

Vir14

Answer 3

Hi Vir14,

This behavior of the datepicker is by design. We can format the datetime value passed from the datapicker to the dataset query, however, it is impossible to change the format of the datetime displayed in the datepicker input box. Based on my test, if the input value ends with a slash like "11/29/", the datepicker will use the short time format instead of the datetimeoffset format.

Here, I would suggest you submitting a wish at https://connect.microsoft.com/sql.

Connect site is a connection point between you and Microsoft, and ultimately the larger community. Your feedback enables Microsoft to make software and services the best that they can be, and you can learn about and contribute to exciting projects.

Thanks for your understanding.

Regards,

---

Mike Yin
TechNet Community Support