Solved

How to convert string field to datetime in Reporting Services?

Posted on 2007-03-20
3
8,843 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

810 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