September 28, 2011 at 1:52 am
Hi,
I want to export some query result to excel file. I can use some utilities like sp_makewebtask or bcp export or openrowset. But none of this utilities gives the exact data in sheet. means the datetime in database table is appearing in excel cell as different format. So is there any way to format a cell in excel using sql quries(I am using sql server 2005)
Regards,
Vaithilingam.K
September 28, 2011 at 2:05 am
Not in SQL, as far as I know. But you could write some VB and embed it in the workbook so that the format is changed whenever the cell is upated.
John
September 29, 2011 at 8:45 am
With limitations this can be done directly in the SQL in the result set SELECT statement, and only if the target output of the process will always be Excel.
If you just need to have a date value display in a particular format and don't need it to actually have anything calculated from it once it is moved to Excel, then in the SELECT statement convert the datetime value to varchar field with a standard format and append an apostrophe in front of it so Excel will treat it as a text field. An example:
SELECT '''' + IsNull(CONVERT(CHAR(10), x.SomeDateTimeColumn, 101), '') 'ExcelDate'
Another method if you actually need Excel to be able to recognize the date coming from SQL as a valid datetime and do calculations from it once it is in Excel, is to convert it in the SQL output to the base Excel date. This will look odd in Excel at first but it can be formatted (manually) to whatever date display style is required and Excel will actually recognize it as a date. An example:
SELECT DATEDIFF(day, '1/1/1900', x.SomeDateTimeColumn) + 2 'ExcelDate'
maddog
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply