ssrs 2012 date formatting

  • wendy elizabeth

    SSCoach

    Points: 16894

    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?

  • Thom A

    SSC Guru

    Points: 98272

    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.

  • wendy elizabeth

    SSCoach

    Points: 16894

    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: 98272

    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.

  • wendy elizabeth

    SSCoach

    Points: 16894

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

  • rVadim

    Hall of Fame

    Points: 3896

    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: 98272

    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.

  • pietlinden

    SSC Guru

    Points: 62394

    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: 62394

    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: 90000

    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: 62394

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

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

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