March 1, 2012 at 12:14 pm
Hi,
I Need a SP to export data from SQL Server to Excel. I think the function operowset in a good idea. But when, I Export data with a type DateTime or Int, in Excel is showed like text. What I need to do, to these datas are displayed as original type?
this is my test code:
Declare @PathArqXls varchar(200),
@SQLOrigem varchar(2000),
@SQLDestino varchar(2000),
@CmdSql varchar(5000)
Set @PathArqXls = 'C:\FAMTAB.XLS'
Set @SQLOrigem = 'Select * from FAMTAB'
Set @SQLDestino = 'SELECT TprCod,FamCod,FamDes,FamUsuLog,FamUsuDta,FamUltUsu,FamUltDta FROM [PLAN1$]'
--TprCod and FamCod are Int
--FamUsuDta and FamUltDta are DateTime
Set @CmdSql = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;Database=' + @PathArqXls + ''',''' + @SQLDestino +''')' + @SQLOrigem
March 1, 2012 at 5:25 pm
Whenever a date is dropped into Excel from SQL it is in the full date format. You'll want to cast or convert the datetime column in your query. If you are on 2008, which I assume since this is a 2008 forum, just:
cast(YourDateField as DATE)
the DATE datetype doesn't have the timestamp.
If you aren't on 2008, then something like:
convert(varchar(10),YourDateField,101)
which will convert the date to a text string in the format mm/dd/yyyy. Look up cast/convert in BOL for additional date formats suitable for your region.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply