Trying to change time format in SSRS

sqlcurious
sqlcurious used Ask the Experts™
on
Hi Experts, I tried to change the format of the date for ex: '2001-08-21 00:00:00.000' in the stored procedure by doing convert function. It works fine when I execute the sp but when I look at the report it still shows '2001-08-21 00:00:00.000' format.
So, I tried changing in the report under Expr->Common functions->date time -> FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate), though its working for all the dates but then the null values are coming out to be '1/1/0001', I am not understanding that, can you please help.
Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Hi

SSRS does not understand NULL in date format(outside of its range so it goes default which is what you get).

Try using IF statement to check for null and if yes go with empty string "" 

Hope that helps
Emil
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Commented:
Please check NULL first and then apply expression.

=IIF(ISnothing(Field.Value), '',FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate))

Author

Commented:
Hi PatelAlpesh and itcouple, thanks for the answers, I am giving equal points for both.
As Alpesh gave the full expression icouple mentioned about the double quotes. So, the answer that
worked for me was:
=IIF(ISnothing(Field.Value), " ",FormatDateTime(Fields!BirthDate.Value, DateFormat.ShortDate))

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial