Post reply

DTS Date

  • May 22, 2004 at 12:18 pm

    #359815

    Hello,

    I am trying to output data from my sql table to an excel spreadsheet and send it by email which works fine, the problem is he wants the date to be in the format d-mmm-yy, which is easy to format in excel manually, but he do not want to do this manually.  I tried to do this when I select the date from the table to spreadsheet, "select  convert(char,value_date,106) from table", but this don't get transported to the excel spreadsheet, I get my results on the spread sheet as dd/mm/yy.  Can you please help either to set the date on excel forever to be in this format "d-mmm-yy" or to force this output to excel

  • May 25, 2004 at 8:00 am

    This was removed by the editor as SPAM

  • May 26, 2004 at 7:27 am

    #507846

    Hi

    It might be worth checking your Settings/Control Panel/Regional Options/Date format. I guess this is what Excel uses as default when you export a date type column to it. Change dd/MM/yyyy to d-mmm-yy and change the separator to '-'

    Hope this helps

    rich

  • May 26, 2004 at 8:19 am

    #507857

    Excel attempts to be clever by recognizing dates and automatically formatting it to what the computers regional settings are.

    So you have a few options –

    a. Change the users regional settings (not one I would suggest)

    b. Force the DTS package to put ‘ before the date, thus making Excel think it is a string.

    c. Create an auto_open Macro on the spreadsheet so any formatting is done automatically when the user opens it.

    Personally I prefer the last option because then I can do more formatting to make it look good for the user. The only issue with that is you have to truncate and populate the same spreadsheet each time. On the other hand the second option will leave you with much less maintenance.

    Hope this helps.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply