Solved

Format String to Date SSRS 2005

Posted on 2010-09-15
5
559 Views
Last Modified: 2012-05-10
I need assistance on the code below:
=Switch
(
    Fields!Well_Child_Visit_Adolescent.Value = 9999,"N/A",
    Fields!Well_Child_Visit_Adolescent.Value = 0000,CStr("12/31/2010"),
    Fields!Well_Child_Visit_Adolescent.Value = 0001,DateAdd("D",730,Fields!Birthdate.Value),
    Fields!Well_Child_Visit_Adolescent.Value = 0002,"Past Due",
    Fields!Well_Child_Visit_Adolescent.Value,Fields!Well_Child_Visit_Adolescent.Value
   
   
)

The last item in the switch returns the data in a string for example(20100625) to represent the date as it should. I need the end-user to see the string formatted as the following : 06/25/2010. I need the syntax to accomplish this is Reporting Services in the Switch Function. Please Help!!! I am beating my head against the wall ;-)
0
Comment
Question by:VTFatcat1973
  • 3
5 Comments
 
LVL 4

Expert Comment

by:BostonMA
Comment Utility
Try?

format(Cdate(Fields!Well_Child_Visit_Adolescent.Value
), "MMMMMMMM dd, yyyy")
0
 

Author Comment

by:VTFatcat1973
Comment Utility
It does not like that ;-( I get the following error cannot convert string to date type. I believe that I am going to have to use left, right functions, I am just not sure how to do it ;-)
0
 
LVL 6

Accepted Solution

by:
AliSyed earned 500 total points
Comment Utility
=Format( CDate( Left( Fields!Well_Child_Visit_Adolescent.Value , 4 ) & "/" &
 Mid( Fields!Well_Child_Visit_Adolescent.Value , 5, 2 ) & "/" &
 Right( Fields!Well_Child_Visit_Adolescent.Value , 2 )), "yyyy/MM/dd" )
0
 

Author Comment

by:VTFatcat1973
Comment Utility
Thank-you so much- I had to modify it just a bit- but it works great. here is my final code
=Switch
(
    Fields!Well_Child_Visit_Adolescent.Value = 9999,"N/A",
    Fields!Well_Child_Visit_Adolescent.Value = 0000,CStr("12/31/2010"),
    Fields!Well_Child_Visit_Adolescent.Value = 0001,DateAdd("D",730,Fields!Birthdate.Value),
    Fields!Well_Child_Visit_Adolescent.Value = 0002,"Past Due",
    Fields!Well_Child_Visit_Adolescent.Value,Format( Left( Fields!Well_Child_Visit_Adolescent.Value , 4 ) & "/" &
 Mid( Fields!Well_Child_Visit_Adolescent.Value , 5, 2 ) & "/" &
 Right( Fields!Well_Child_Visit_Adolescent.Value , 2 ) )

   
)
0
 

Author Closing Comment

by:VTFatcat1973
Comment Utility
Awesome ;-)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now