Change Date Formats 1

[pluto1415]

We're using SQL 2005 - we're in the U.S.A. One of the many reports we generate needs to be exported to Excel and emailed to a company in England. They have requested that the dates in their report be in the dd/mm/yyyy format (as opposed to mm/dd/yyyy).

I cannot figure out how to change this without having to open the Excel file and change it there. Is there a way to change the date formats for just this one report?

 

[RiverGuy]

This might provide some info you need:

http://www.chrismay.org/2006/10/09/Make+Use+Of+Culture+In+SQL+Reporting+Services+Local+Reports.aspx

 

[pluto1415]

I've changed the Language part of the report properties to be English (United Kingdom) and redeployed the report, but when I run it, the date still comes out as 6/15/2010 instead of 15/06/2010.

Any other ideas?

 

[ousoonerjoe]

Have you tried converting the format? If the data is still coming out in US format then Excel may be reading your region settings and converting it back to US style.

CONVERT(VARCHAR(10), DateField, 103)

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[pluto1415]

I haven't even tried exporting to Excel yet, I'm assuming I need to get it to show in Report Manager in the correct format - otherwise it won't dump correctly to Excel.

I can use your convert statement in the SQL datasource though, correct? I'm going to try that.

 

[pluto1415]

YAY! I added your Convert() statement to my SQL select statement and it did exactly what I needed it to do. Thanks so much for your help.

 

[ousoonerjoe]

CONVERT will format DATETIME to different styles. The help file spells out which argument does what. Just remember to convert the output to a VARCHAR or it returns the local format.

--------------------------------------------------
Bluto: What? Over? Did you say "over"? Nothing is over until we decide it is! Was it over when the Germans bombed Pearl Harbor? No!
Otter: Germans?
Boon: Forget it, he's rolling.
--------------------------------------------------

 

[xlbo]

Could you not just format the field in the report to dd/MM/yyyy

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[DaveInIowa]

Using a date format of "d" will take into account the report's culture information and format the date accordingly.