Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Date Format in SSRS Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 5:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:56 PM
Points: 32, Visits: 184
I have the date time field. I want extract only Month and the year. How can I do the same.

Current format of the field is "10/01/2012 hh:mm:ss"
Need to convert to "October 2012"

I want to do these changes in SSRS.
Post #1367349
Posted Tuesday, October 2, 2012 6:19 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 1,184, Visits: 1,220
deepeshdhake (10/2/2012)
I have the date time field. I want extract only Month and the year. How can I do the same.

Current format of the field is "10/01/2012 hh:mm:ss"
Need to convert to "October 2012"

I want to do these changes in SSRS.


Assuming your source data field is a datetime datatype and you don't want the day, you could do something like:

=Format(Fields!YourDate.Value, "MMMM") & " " & CStr(Year(Fields!YourDate.Value))

HTH,
Rob

*I don't have SSRS open as I type this, so please double check my memory to make sure it works as you wish
Post #1367355
Posted Tuesday, October 2, 2012 8:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:56 PM
Points: 32, Visits: 184
Hello Rob,

Few data is correct and few is wrong. Here is the sample output I am getting:

Octaber 12
Jul12 12
SetpAmer

What is the problem?
Post #1367379
Posted Tuesday, October 2, 2012 10:00 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 1,184, Visits: 1,220
Impossible to know without seeing the data, but I'd guess that the field isn't a datetime datatype, but rather a varchar() where not all of the values are correctly formatted dates.

Take a look at the raw data coming through for that field.
Post #1367388
Posted Tuesday, October 2, 2012 10:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:56 PM
Points: 32, Visits: 184
Hello,

Originally, that column has Date as Data type. So, now what to do? IS there any other procedure to do this?
Post #1367401
Posted Wednesday, October 3, 2012 2:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
=FORMAT(Field!DateField.Value "MMMM yy")

How about the above?




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1367457
Posted Wednesday, October 3, 2012 10:11 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:56 PM
Points: 32, Visits: 184
This too doesn't work. Is there any other way to do this.

With Format(), I am getting some thing like this:

OctAber
SetAmber
etc
Post #1367831
Posted Wednesday, October 3, 2012 12:20 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 1,184, Visits: 1,220
I still think you have data that isn't a date. Can you take the SQL out of your dataset and manually run it in SMSS? What does that data look like in that field?

Rob
Post #1367941
Posted Wednesday, October 3, 2012 2:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 1:56 PM
Points: 32, Visits: 184
Hello Rob,

Can u also tell how to covert if the data type is varchar.

Thanks in advance.
Post #1368045
Posted Thursday, October 4, 2012 5:34 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 1,184, Visits: 1,220
In SSRS you would use the CDate() function to change from varchar() to datetime; but the issue is that you have non-date values (Amber) mixed into that column. Check your source data, see what that shows.

HTH,
Rob
Post #1368317
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse