Importing specific date format to Excel from Microsoft SQL Server

Question

I've already asked the question at: StackOverFlow and got sent to this place here.

I'm importing dates from a German SQL Server table into a German Excel file via the built-in Excel connection tool.

However the date format is just like in the SQL Server: 2012-08-08 but I want to display: 08.08.2012. When I double-click inside a cell it will recognize the German date formatting but of course I would like to have that format for the entire column in the beginning without having to manually change it.

I also need to be able to use these dates for calculations.

I know there is a text box where you can enter a SQL "Definition" in the Excel Connection tool but it doesn't really work with "normal" SQL-statements.

Do I need to change something in SQL Server or how do I make this work?

Solution

If I understand you correctly, you're saying the field in SQL Server is stored in datetime format, and you want to pull that info into Excel by creating a connection to your SQL Server table, and display it in German date form dd.mm.yyyy

You shouldn't need to edit the SQL in the definition tab of the Connection Properties window for formatting purposes - this may be simpler than you think.

1. Use Excel's Data Connection Wizard to pull in the table you want to show in Excel.

2. Then in Excel, for each date column, select the entire column, click format cells (or the handy keyboard shortcut is Ctrl+1), then on the Number tab click Custom at the bottom of the column on the left. In the "Type" text box, enter: dd.mm.yyyy

3. The next time you refresh the data, Excel should retain the German formatting of your dates. You can use anything in these columns for calculations, Excel knows they are dates.

Comments

[Jared]

As a standard practice it is ussually easier/faster to change how something is being done in the database rather than trying to mess around with it after it has been extracted. I would try to research changing how the date is stored in SQL Server, which shouldn't be that hard to do. Also, welcome to SuperUser!

[TonyC]

I've already tried my luck there. When I set-up a test table and insert values like this: insert into [dbo].[DateTest] Values ('2011-12-01', '01.12.2011'). The output is always the first format. With DBCC USEROPTIONS I can see the dateformat is set to dmy. What do I need to modify? A little example would be really nice. Thank you.

[Iszi]

For future reference, please don't cross-post. You should instead flag your question for migration.

[Jared]

apparently SQL server has built in styles for this. Here is how to select a date and convert it to german. SELECT CONVERT(varchar, getdate(), 4)

[TonyC]

and where exactly do I use this statement in the excel connection tool? If yes, what is the right syntax for it?