MS SQL Server Dates Excel

Question

I have data this is linked from SQL Server into an excel document. The column format on the SQL Server is datetime2.

When I get the data via an ODBC connection it comes across as a string?

I tried using

CAST(column AS DATE )

but that didn't work.

I tried reformatting via

CONVERT(VARCHAR(10), column, 103)

as well but that didn't work.

I tried retrieving the data via Microsoft query as well but that didn't work.

At the moment I am using VBA code like:

While (ActiveCell.Value <> "")
  ActiveCell.Value = DATEVALUE(ActiveCell.Value)
  ActiveCell.Offset(1,0).Activate
Wend

and looping through each column that needs this treatment but 100000 rows in multiple columns takes forever to loop through. Are there any alternatives?

You might find this post helpful. If you stick with your current method make sure ScreenUpdating=False is set before your loop. — Automate This
– Automate This, Commented Oct 25, 2013 at 4:30

KillerSnail · Accepted Answer · 2013-10-29 09:09:30Z

2

It appears that Excel and Access as well don't like the SQL Server datetime2 format.

I have converted the data into datetime format and that seems to be working now.

answered Oct 29, 2013 at 9:09

KillerSnail

3,64113 gold badges51 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sandr · Accepted Answer · 2013-10-29 09:51:36Z

1

You can simply convert date in the Excel to the following format:

"YYYY-MM-DD HH:mm:ss"

This is the default ODBC canonical format. In this case the implicit conversion will be made and the correct results will be achieved.

Another way is to try to perform convert using another style:

CONVERT(VARCHAR(10), column, 120)

answered Oct 29, 2013 at 9:51

Sandr

7761 gold badge6 silver badges10 bronze badges

Comments

Adrian Sullivan · Accepted Answer · 2013-10-25 07:20:22Z

0

No MACRO's required, us Excel table columns instead.

Any ODBC connections in Excel returns a Table. Simply add a new column to the right of your table with a calculation. It should look something like this =DATEVALUE([@DateColumnFromODBC])

The column will add and shrink with your table and always retain it's reference as long as the ODBC connection stays the same.

answered Oct 25, 2013 at 7:20

Adrian Sullivan

3973 silver badges6 bronze badges

Collectives™ on Stack Overflow

MS SQL Server Dates Excel

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related