December 17, 2012 at 5:42 pm
I'm having issues when importing data from SQL Server into Excel 2012. The datetime2 fileds appear to be imported as text so the users are unable to format the date.
Has anyone else come across this?
The only solution I can see at the moment is to convert the fields to datetime before the import.
December 17, 2012 at 7:28 pm
Personally I've never tried it but a quick test of this:
SELECT CAST('2012-01-01' AS DATETIME2)
If you copy/paste the result into Excel it can be formatted as a date with nor problem.
As annoying as it may seem to you, since CASTing to DATETIME seems to work for you, why argue with success?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 17, 2012 at 7:39 pm
Its annoying because it means we'd have to cast or convert every datetime2 date that could appear on the spreadsheet(s).
The developers will argue it'd be quicker to change the data type to datetime.
December 17, 2012 at 7:44 pm
Chris-475469 (12/17/2012)
Its annoying because it means we'd have to cast or convert every datetime2 date that could appear on the spreadsheet(s).The developers will argue it'd be quicker to change the data type to datetime.
Personally I wouldn't let the developers argue about what makes their life easier. If there's a business need for the extra precision, that trumps.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 17, 2012 at 7:49 pm
It's more an issue of we asked the developers to use datetime2 going forward. The precision isn't really an issue.
It looks like we'll be having another discussion on datetime vs datetime2.
December 17, 2012 at 8:28 pm
Funnily enough if I copy the results directly from SSMS to Excel the datetime2 coumn is fine and I can format the dates.
I can't see why this wouldn't be the same when importing the data directly into Excel.
December 17, 2012 at 8:37 pm
Chris-475469 (12/17/2012)
It's more an issue of we asked the developers to use datetime2 going forward. The precision isn't really an issue.It looks like we'll be having another discussion on datetime vs datetime2.
You'll find some other annoyances with respect to DATETIME2 I suspect, like I think you can't add +1 (for one day) directly to it like you can with DATETIME, not to mention that it takes up more bytes. It sounds like the decision to move forward strictly with DATETIME2 wasn't made on its merits.
Chris-475469 (12/17/2012)
Funnily enough if I copy the results directly from SSMS to Excel the datetime2 coumn is fine and I can format the dates.I can't see why this wouldn't be the same when importing the data directly into Excel.
Sounds like what I said in my original response. 😎
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 17, 2012 at 8:46 pm
Yeah you're right!
I thought datetime2 was supposed to consume less bytes than datetime depending on the precision used?
I'll check MSDN again.
December 17, 2012 at 8:50 pm
Chris-475469 (12/17/2012)
Yeah you're right!I thought datetime2 was supposed to consume less bytes than datetime depending on the precision used?
I'll check MSDN again.
Actually I didn't check the byte-size difference - that was me speculating.
But I did check the +1 and it won't work. Neither will adding 1.0.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply