Solved

How to convert string field to datetime in Reporting Services?

Posted on 2007-03-20
3
9,247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
How to increase the row limit in Jasper Server.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 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