SSRS 2012 date formatting

Question

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

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.
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?

score 2 · Accepted Answer · answered Apr 21 '19 at 13:07

2

This TSQL will work with SQL 2012, the language for SQL server is Español in this example.

SET DATEFORMAT yyyymmdd
DECLARE @date as DATE
SET @date = '20190603'
SELECT @@LANGUAGE AS SQL_Language, FORMAT ( @date, 'D') AS 'Date'

The results set returned is this

The FORMAT() function can output datetime in a variety of formats for more details see this web page.

SSRS reports can also support datetime formatting if required. See this answer for more details.

answered Apr 21 '19 at 13:07

SQLBobScot

694
5
20

1

From your example, how does the sql know the language is Spanish/ – user1816979 Apr 22 '19 at 01:49
1

Can the logic be in ssrs and not in the t-sql? can you show me how to accomplish goal – user1816979 Apr 22 '19 at 01:51
1

@user1816979 I used the following TSQL command to set the language to Spanish. SET LANGUAGE Español; – SQLBobScot Apr 23 '19 at 11:59
1

In an SSRS report add the find the date value from the dataset. (1) Click once with right-hand mouse button on the text box where date value is displayed. (2) Click on 'Text Properties..' to open 'Text Box Properties' (3) Select 'Number' (4) From the list in 'Type:' select the format you want to display See - https://imgur.com/a/h0ov4Gu Note in the example the report language is set to 'es-ES' – SQLBobScot Apr 23 '19 at 12:56

score 1 · Answer 2 · answered Apr 22 '19 at 20:21

I think it's better to let SSRS do the formatting. You also won't need to update the stored procedure or view.

For your desired format

I would like a date like 06/03/2019 to look like Monday, June 3, 2019

You can set the FORMAT property of the Text Box to

dddd, MMMM d, yyyy

The format should be the same when using Spanish settings - except in Spanish.

SSRS 2012 date formatting

2 Answers2