ssrs 2012 date formatting

Question

ssrs 2012 date formatting

wendy elizabeth

SSCoach

Points: 16915
More actions
April 20, 2019 at 3:33 am

#3475010

In an ssrs 2012, I would like date to change a date to be in a specific format without changing the stored procedure that obtains the sql server date. This is due to the fact the stored procedure is extremely complex and I do not want to change it unless I absolutely have to.
Basically I would like the following:
1. if the the date is in a basic datetime format, I would like
a date like 06/03/2019 to look like Monday, June 3, 2019 for the English formatting.
2. For a Spanish version of the date is 6/3/2019, I would like the Spanish version of the date to be in the same format.
Thus would you show me the sql on how to accomplish this goal?

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

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

Are you talking about how the date would be displayed in a textbox to the user in the report? I honestly can't remember if SSRS (2012), detects the user's language for the display of the report. If it does, then change the format of your date fields to the format dddd, MMMM d, yyyy.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

wendy elizabeth SSCoach Points: 16915 More actions · Answer 2

can you tell me if ssrs reports translate the dates for us? if so, how do you accomplish this goal?

Thom A SSC Guru Points: 99126 More actions · Answer 3

wendy elizabeth wrote:

can you tell me if ssrs reports translate the dates for us? if so, how do you accomplish this goal?

Did what I suggested above not work then?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

wendy elizabeth SSCoach Points: 16915 More actions · Answer 4

I wish what you guested did work. But no that did not work.

rVadim Hall of Fame Points: 3969 More actions · Answer 5

There are formatting functions. You should be able to convert to any format you want.

=Format(Fields!MyDate.Value, "dddd, MMM d yyyy")

--Vadim R.

Thom A SSC Guru Points: 99126 More actions · Answer 6

rVadim wrote:

There are formatting functions. You should be able to convert to any format you want.
=Format(Fields!MyDate.Value, "dddd, MMM d yyyy")

That's effectively identical to what I suggested. The OP didn't give any details on why it didn't work, however, my guess is it wasn't using the users language but the report's. That is, however, a total guess.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

pietlinden SSC Guru Points: 63428 More actions · Answer 7

From what I've read, Language is a report-wide setting. So the only way I could think of doing it is to use either SWITCH or a lookup.

I sort of got it to work like this:

=Format(Day(Fields!TheDate.Value),"00") & "-" & Choose(Month(Fields!TheDate.Value),"enero","febrero","marzo","abril","mayo","junio","julio","agosto","septiembre","octubre","noviembre","diciembre") & "-" & Year(Fields!TheDate.Value)

If you wanted a short date, I guess you could use LEFT() in the MonthName calculation.

pietlinden SSC Guru Points: 63428 More actions · Answer 8

For grins, I wrote this in VB.NET (well, sort of)

Public Function FormattedDate (ByVal dtInputDate As Date, ByVal blnShort As Boolean) As String
    Dim m As Byte
    Dim d As Byte
    Dim y As Integer
    Dim Mo As String
    Dim DayName As String
    
DayName = Choose(Weekday(dtInputDate ,1),"lunes","martes","miercoles","jueves","viernes","sabado","domingo")
    m = DatePart("m", dtInputDate)
    d = DatePart("d", dtInputDate)
    y = DatePart("yyyy", dtInputDate)
    
    Select Case m
    Case 1
        Mo = "enero"
    Case 2
        Mo = "febrero"
    Case 3
        Mo = "marzo"
    Case 4
        Mo = "abril"
    Case 5
        Mo = "mayo"
    Case 6
        Mo = "junio"
    Case 7
        Mo = "julio"
    Case 8
        Mo = "agosto"
    Case 9
        Mo = "septiembre"
    Case 10
        Mo = "octubre"
    Case 11
        Mo = "noviembre"
    Case 12
        Mo = "diciembre"
End Select
    if blnShort  then
        Mo = left(Mo,3)
    end if    
    FormattedDate = DayName & ", el " & CStr(d) & " de " & Mo & " " & y
End Function

Sample output viernes, el 3 de enero

(don't know how to do the underlined superscripted 0 in SSRS that would go after the day number of the month).

Not the greatest, but I think it gets the job done, and you don't have to worry about which language your local settings are set to (I'm assuming they're English) … Yeah, I know, I'm missing the accents here and there... (do you have to use HTML-style values for them?)

Sue_H SSC Guru Points: 90860 More actions · Answer 9

wendy elizabeth wrote:

can you tell me if ssrs reports translate the dates for us? if so, how do you accomplish this goal?

You can set it at the report level or the text box. If nothing is specified, the locale of the report server is used. It's explained in the first paragraph of this document:

Set the Locale for a Report or Text Box (Reporting Services)

So you can set it for an individual text box using formatting functions as explained in the previous posts. But there is no way to tell what's "not working".

Sue

pietlinden SSC Guru Points: 63428 More actions · Answer 10

Oh! I knew it had to be easier than I was making it!