You can use the Format() function to achieve this. If you select the field you want to format and then go to Properties->Expressions you could do something like this:
=Format(Fields!DateCreated.Value,"yyyy/MM/dd")
You can find more information on the Format function below under the Date Functions sub-heading:
http://msdn.microsoft.com/en-us/library/ms157328.aspx
I hope this helps you.
I understand your problem to be how to successfully convert the string into a DATE value. An undelimited string of integers is assumed to be Year-Month-Day order. (And, of course, I agree with the comments that dates should be stored in DATE data types in the database.)
Reviewing the MSDN CONVERT documentation does not show a built-in conversion for your string, but it is easy to work around.
http://msdn.microsoft.com/en-us/library/ms187928.aspx -- CAST and CONVERT
I changed the month to 8 to make it easier to double check. Using the CONVERT style option 3, you can do the following:
DECLARE @String VARCHAR(10);
DECLARE @DateValue DATE;
SET @String = '250809';
-- Convert your undelimited string DDMMYY into a DATE
-- First: Add / between the string parts.
SET @STRING = SUBSTRING(@String,1,2)+'/'+
SUBSTRING(@String,3,2)+'/'+SUBSTRING(@String,5,2);
-- Second: Convert using STYLE 3 to get DD/MM/YY interpretation
SELECT @DateValue = CONVERT(Date, @String, 3);
-- Using the DATE
-- Select the value in default Year-Month-Day
SELECT @DateValue AS DefaultFormat;
-- Select the value formatted as dd/mm/yy
SELECT CONVERT(VARCHAR(20),@DateValue,3) AS [DD/MM/YY];
The results of the last two selects are:
DefaultFormat
-------------
2009-08-25
DD/MM/YY
--------
25/08/09
To get your DATE formatted in the way you want it, you have to insert the '/' delimiters then use the STYLE 3 in converting from the string to the DATE. (I am sure that there are other workarounds and conversion styles that would work as well.)
Likewise when displaying the DATE as you desire, you need to use STYLE 3
Best Answer
Plan A: Clean up the dates as you load the data. How are you loading it?
INSERT?LOAD DATA INFILE? It is possible to do theSTR_TO_DATE()in either case.Plan B: (This requires "Plan A" for future inserts.) Devise a script, possibly mostly inside a Stored Routine that would do the following for any given column known to be a VARCHAR that should be a DATE. (Do something similar for a DATETIME.) Here is a sketch of what is needed for one $column in one $table:
ALTER TABLE $table ADD COLUMN tmp DATE NOT NULL;UPDATE $table SET tmp = STR_TO_DATE($column, '%d/%m/%Y');ALTER TABLE $table DROP COLUMN $column, RENAME COLUMN tmp TO $column;