Solved

How to convert string field to datetime in Reporting Services?

Posted on 2007-03-20
3
8,723 Views
Last Modified: 2012-06-22
I am pulling data from a database whose field is not in the bes format.

Specifically, it is in this text format although it should be a datetime...

[year][month][day]

20070305 for 2007, 3rd month, 5 day.

How do I convert it to a date or datetime field for display in Reporting Services?
0
Comment
Question by:M3hcSS
3 Comments
 
LVL 18

Accepted Solution

by:
chrismc earned 125 total points
ID: 18757693
Ideally you'd do this from SQL. Convert(DateTime, myDate) will probably do it. Then it would be a true datetime field and formatting would then be automatic to your location, i.e. US = m d y, almost everywhere else = dd mm yy.

You can use "Substring" in SQL or "Mid" in VB to extract parts of the string and reformat it the way you want.
The most universal format for storing dates is yyyy-mm-dd. If you can get it in this format then date functions and formatting will work properly.

Cheers
Chris
0
 
LVL 21

Assisted Solution

by:Yurich
Yurich earned 125 total points
ID: 18760381
copletely agree with Chris that you should do it on the SQL side whenever it's possible, but in the case you have to do it in your report (you got no access to your SP, or no rights, or whatever), here is the formula:

=Format( CDate( Left( Parameters!param.Value, 4 ) & "/" &
 Mid( Parameters!param.Value, 5, 2 ) & "/" &
 Right( Parameters!param.Value, 2 )), "yyyy/MM/dd" )

Parameter!param.Value is your "20050305" string, you can change it to your field name. By default CDate will produce date time in the format yyyy-MM-dd HH:mm:ss, so I used Format as well, just to have date part.

Good luck,
Yurich
0
 
LVL 2

Author Comment

by:M3hcSS
ID: 18764644
Both these solutions work very well.

However, the second solution posted by Yurich requires fixed-width fields; if the date is missing, say, leading zeroes then it won't work.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

757 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

20 Experts available now in Live!

Get 1:1 Help Now