MSSQL 2008 datetime2 and date formats to Excel?

[kjv1611]

We've now got some dates being stored in datetime2 and date formats in SQL which works great for SQL Server. However, if you import in a table from SQL to Excel 2010, for instance, it doesn't seem to understand the new date formats (even though this is Office 2010 dealing with SQL Server 2008). So Excel pulls the values in as text values rather than date values.

Other than using CAST/CONVERT on SQL before pulling into Excel, does anyone have any other suggestions for automatically dealing with these? Right now, I'm seeing that I can do this:

SELECT CAST(MyDateField AS datetime) AS MyDateField
FROM MyTable

Within the query pulling the data from SQL Server to Excel.

Thanks for any thoughts. Just thought I'd ask in case someone else had run into this already, and had a better way.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Steve,

I'd venture to say that this is not Excel, but rather your query.

1) try

SELECT MyDateField AS MyDateField
FROM MyTable

and see what results you get. If it does NOT return a NUMBER that can be FORMATTED as the expected date, then

2) try with your original resultset, containing TEXT values, a) ENTER the number ONE in an empty cell b) COPY the cell, c) SELECT the text data d) right-click > Paste Special > MULTIPLY. This will coerce the conversion of the string to a date using either yyyy/mm/dd or mm/dd/yyyy as the implied structure of the TEXT.




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

How are you importing the table from SQL Server to Excel?
My guess is that is where the problem lies
I can query data in DateTime2 or Date format, copy the results and paste into excel and it sees both types as dates


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

 

[fredericofonseca]

hum... I would like to know how both of you get the data on the correct format using a query (not copy and paste)
or even using c+p if dates are prior to 1901.

if I query from the following table into excel 2010, either using get external data from sql server or using ms query, the date and datetime fields always get formatted as text, and those cells that have a date of 1700 always yell a #value if I try do add/subtract a day from them or if I try to multiply by 1

CREATE TABLE [dbo].[test2](
	[dt1] [date] NULL,
	[dt2] [datetime] NULL,
	[dt3] [datetime2](7) NULL,
	[dt4] [datetime2](2) NULL,
	[dt5] [datetime2](5) NULL,
	[dt6] [smalldatetime] NULL
) ON [PRIMARY]

insert into test2 
values
('2001-05-25'
,'2001-05-25 19:09:11.100'
,'2001-05-25 19:09:11.1000000'
,'2001-05-25 19:09:11.10'
,'2001-05-25 19:09:11.10000'
,'2001-05-25 19:09:00'
)
insert into test2 
values
('1901-05-25'
,'1901-05-25 19:09:11.100'
,'1901-05-25 19:09:11.1000000'
,'1901-05-25 19:09:11.10'
,'1901-05-25 19:09:11.10000'
,'1901-05-25 19:09:00'
)

insert into test2 
values
('1701-05-25'
,'1901-05-25 19:09:11.100'
,'1701-05-25 19:09:11.1000000'
,'1701-05-25 19:09:11.10'
,'1701-05-25 19:09:11.10000'
,'1901-05-25 19:09:00'
)



select * from test2
order by dt1

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[xlbo]

all I was doing was running the query in management studio and then copy / pasting the dat ainto excel
Excel then sees the format as date / time and I can format accordingly

The only entry that shows as text is 1701-05-25

that's why I am saying that it is almost certainly the driver performing the query that has the issues with the formats

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

 

[kjv1611]

Sorry, haven't even come back to look at this one yet.

Yes, I can copy/paste the data that way, but when I use Data - Get External Data, and use SQL instead of a table, from SQL Server, I get text values for datetime2 and date value fields, but not datetime.

The exact same query runs without issues in SSMS.

Here's the exact steps I've been taking to import via query into Excel:
[ol 1]
[li]Write query in SSMS to give place to test, wouldn't be necessary with this short of a query, normally[/li]
[li]In Excel, click Data Tab[/li]
[li]Click Other Sources under Get External Data group[/li]
[li]From SQL Server[/li]
[li]Type in the server name in Server name box, leave logon credentials set to default of use Windows Authentication, click Next[/li]
[li]Change database name to desired database in next screen, and click Next[/li]
[li]Uncheck Connect to a specific table[/li]
[li]Click Finish[/li]
[li]If asked, click yes to replace existing connection file[/li]
[li]Click any table (or just allow it to default to first table), and click OK[/li]
[li]On next screen, click Properties[/li]
[li]Definition tab, then change Command Type to SQL[/li]
[li]Type or paste SQL into Command text field[/li]
[li]Click OK, Yes, OK, then see data in spreadsheet.[/li]
[/ol]

I've done this multiple times with various queries, no issues. Places where I have issues is if I need to build a temp table first, or anything along those lines where I'd really have more than one query involved.

But in the given scenario, it's only a very basic SELECT statement, and the CAST command does not seem to work correctly when passed from Excel to SQL. It is not (to my knowledge) pulling in the entire table, and then querying against it. In that case, it'd be using MS Query syntax rather than MSSQL syntax, I assume.. if there's a difference.





All that said... - I'll try Skip's suggestions to see, later on, hopefully, this afternoon/evening.

I'll post back with any further details ASAP.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[xlbo]

hmmm - using teh data that frederico provided and using the method that kjv1611 shows (using SQL Server 2008 R2 and excel 2010), I don't get any problems - all cells formatted as date/time and showing correct values...

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

 

[fredericofonseca]

That is the method I did also - no options selected, nothing but the defaults - and that was the result I got - the contents looks good but its not a date on the cases I mentioned

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[xlbo]

further analysis shows agreement with frederico in 1 area - excel doesn;t like the 1701 dates!

when I use =VALUE(A1) to interrogate what excel thinks the value of the fields is, again, only the 1701 dates are in error - all others are converted to decimal as I would expect

In terms of the 1701 dates I am not surprised as this is prior tot he date range that excel can handle

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

 

[kjv1611]

Sorry so late replying - was out of town, and basically off the grid for a week. I feel like I should know this, but what are the 1701 dates? Is that just dates prior to 1701, or does that mean something else?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Excel cannot handle dates prior to 1900

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

I don't think any of the dates I was attempting to pull in were that old. The oldest would have been 1/1/1950, as that's been used as a "default" date in some instances.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57