I have an ssrs report with one field as date column( value coming from db is varchar) . but When i export to excel its not retaining the format as date . I tried using Cdate, format date function in expressions & also tried changing text box properties in number to date . how can i ensure that i get the format as date when i right click and check format of that cell . The data is coming fine .When used the above mentioned function its either showing format as general or custom
2
votes
2 Answers
0
votes
Change your data source query so that in converts the varchar date to a date
eg Convert(datetime, MyDate, 103) 103 assumes date is in DD/MM/YY format
alternatively you could try using date serial function
=DateSerial(Cint(Right(mydate(4))), Cint(Mid(mydate(4),4,2)), Cint(Left(mydate,2)))
Where mydate is your date field value, again assumes date is in dd/mm/yyyy format
0
votes
If your dates are in a consistent format throughout your entire dataset the easiest way is to use the CONVERT() function in your SQL query. CONVERT() takes an optional third argument that specifies the style that the source data is in. The style tells the function exactly how to convert values that could take multiple forms (like dates and times). There are too many codes to list here so have a look at the MSDN page linked below to see them all.
For example, if your dates are stored in DD/MM/YY format then you would use code 3. Then you would convert it like this:
SELECT CONVERT(DATE, DateField1, 3) AS dt
FROM tbl
However, if your dates are not consistent things get a bit more complicated. You'll need to look at what kinds of values can appear there and see if there is any way you can identify them programatically.
A more complex example would be that sometimes dates would be DD/MM/YY and other times they will be DD-MM-YY. Also, sometimes it is a four digit year instead of a two digit year which you will notice on the MSDN page is a different style code. (The four digit year is usually the code for the two digit year plus 100.)
You could use a case statement and LIKE pattern matching to convert all of them to a date.
SELECT CASE
WHEN DateField1 LIKE '__/__/____' THEN CONVERT (DATE, DateField1, 103)
WHEN DateField1 LIKE '__/__/__' THEN CONVERT(DATE, DateField1, 3)
WHEN DateField1 LIKE '__-__-____' THEN CONVERT(DATE, DateField1, 105)
WHEN DateField1 LIKE '__-__-__' THEN CONVERT(DATE, DateField1, 5)
END AS dt
FROM tbl
These patterns won't match dates where the day or month is only a single digit and the code above probably isn't going to solve your problem. You need to see what your data looks like and write a bit of SQL to get it into the correct format.
Really, whoever designed the database shouldn't have put a date in string datatype... but sometimes you just have to work with what you've got.
References for what I've been talking about:
