Report Builder 3.0: How to convert string representation of dates in mm/dd/yy format using CDate

date reporting-services reportbuilder string-conversion regional-settings
confusedKid picture confusedKid · Mar 15, 2012 · Viewed 28.4k times · Source

I'm building a report using Report Builder 3.0.

The source system I'm working with has strings representing dates in the format of mm/dd/yy, but I want to show this to the end users as dd-MMM-yy instead. Using CDate on the string will give me errors because it's interpreting the string in the dd/mm/yy format (US regional settings).

Is there a way to convert the string to a date correctly without changing the regional settings (this isn't really an option)?

Thanks!

Answer

Hari picture Hari · Mar 27, 2012

You can use Format command and specify the exact format you require. For example:

=Format(Cdate(Fields!Date.Value),"dd-MM-yyyy")

or you can try this:

=Day(Fields!Date.Value) & "/" & Month(Fields!Date.Value) & "/" & Year(Fields!Date.Value)

EDIT: This is OK:

=Cdate(Mid(Fields!Date.Value,4,2) & "/" & Mid(Fields!Date.Value,1,2) & "/" & Mid(Fields!Date.Value,7,4))

Related questions

how to remove time from datetime

The field DATE in the database has the following format: 2012-11-12 00:00:00 I would like to remove the time from the date and return the date like this: 11/12/2012

Read More
Find first and last day for previous calendar month in SQL Server Reporting Services (VB.Net)

I'm creating a report in MS SQL Server Reporting Services and need to set the default Start and End Date report parameters to be the first and last dates of the previous calendar month and need help. The report is …

Read More
How to calculate number of days by two dates in SSRS formula field

I have an SSRS report and I have a table there, in which I have a column named as No. Of Days which are lying between two dates. I am getting those dates (datetime) through datasource but don't know how …

Read More