May 4, 2011 at 6:46 am
I run a lot of ad hoc queries for others which I then copy into an Excel spreadsheet to send to them. Any time there is a date field, I have to manipulate the column to tell Excel that it is a date.
Is there any way to prepare the date beforehand in the query, (or any setting in Excel) that will allow it to recognize it as a date without my having to manipulate it?
Ditto for strings that are numbers with leading zeroes. Excel treats them as numbers and strips off the leading zeroes.
Thanks,
*******************
What I lack in youth, I make up for in immaturity!
May 4, 2011 at 6:53 am
For Date Format
http://msdn.microsoft.com/en-us/library/ms187928.aspx
For Export Utility available in SSMS (SQL SERVER STUDIO MANAGEMENT)
Export Data to Excel Videos
http://technet.microsoft.com/en-us/sqlserver/ff686858.aspx
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
May 4, 2011 at 8:37 am
Thanks for the info. I'll try that. Have a great day!
*******************
What I lack in youth, I make up for in immaturity!
May 5, 2011 at 2:21 pm
Okay, I'm sorry if I wasn't clear, but this is what I was really looking for...
Try this:
* run this script in SSMS:
SELECT
GETDATE() AS [Unconverted],
CONVERT(SMALLDATETIME, GETDATE(),101) AS [Converted]
* left click in the upper left hand corner to highlight the whole thing
* right click in the upper left hand corner and choose "Copy with headers"
* open a new spreadsheet in Excel
* click in the upper left cell, right click and select "Paste"
If you'll notice, the unconverted cell displays as a weird number. The converted cell displays as ############. Now click in the upper left hand corner to highlight all, then double click on the first column's right edge to resize.
The converted column is recognized as a date time!
*******************
What I lack in youth, I make up for in immaturity!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply