Date Format in SSRS

Question

Date Format in SSRS

deepeshdhake

SSChasing Mays

Points: 648
More actions
October 2, 2012 at 5:53 pm

#393293

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.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 1

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

[font="Tahoma"]*I don't have SSRS open as I type this, so please double check my memory to make sure it works as you wish[/font]

deepeshdhake SSChasing Mays Points: 648 More actions · Answer 2

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?

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 3

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.

deepeshdhake SSChasing Mays Points: 648 More actions · Answer 4

Hello,

Originally, that column has Date as Data type. So, now what to do? IS there any other procedure to do this?

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

=FORMAT(Field!DateField.Value "MMMM yy")

How about the above?

deepeshdhake SSChasing Mays Points: 648 More actions · Answer 6

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

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 7

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

deepeshdhake SSChasing Mays Points: 648 More actions · Answer 8

Hello Rob,

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

Thanks in advance.

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 9

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

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 10

for the Amber thing, just do a replace command to remove it before you format the date Replace("Amber", "")

Marvin Dillard
Senior Consultant
Claraview Inc