Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to convert string field to datetime in Reporting Services?

Posted on 2007-03-20
3
Medium Priority
?
9,484 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 500 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 500 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
How to increase the row limit in Jasper Server.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

715 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