Post reply

Getting dates from SQL into Excel in float format

  • May 27, 2011 at 2:07 am

    #238264

    HI,

    I have a view with seveal columns, ie a period column (date type in SQL).

    whenever I'm querying this from Excel, I"m getting in the column corresponding to the period a "string" contents (like '2011-05-01'). with this I'm not able to use this as a date...

    I would like to get from SQL the float value of the date. Like this I may format the float value as a date and make pivot tables with Q1, Q2 ....

    How can I achieve this ?

    Many tks for you help !

    PS I have searched already several hours without founding a solution.....

    François

  • May 27, 2011 at 4:12 am

    #1330854

    Removed. I was wrong.

    Sorry.

    -- Gianluca Sartori

  • May 27, 2011 at 4:15 am

    #1330855

    Dates in SQL Server are stored as float: the integer part represents the date, the fractional part represents the time.

    You could try to cast it as float in the query:

    SELECT CAST(datetimeColumn AS float) AS datetimeColumnConverted

    FROM SomeTable

    I don't know how Excel will treat this, but you could try.

    Hope this helps,

    Gianluca

    -- Gianluca Sartori

  • May 27, 2011 at 9:06 am

    #1331055

    sorry not working at all ....

    as date is a float ... not possible to explicitly do a cast...

    so date is stored as a float but retrieved as a date which is a string in Excel..

    ????

  • May 27, 2011 at 9:07 am

    #1331056

    humm,

    please note also that the column is date type noy datetime type...

  • May 27, 2011 at 9:20 am

    #1331062

    OK, I think you have to rethink your strategy a bit.

    You have a date column in Excel and you want it to be filled with a date from SQL Server, right?

    Excel does not seem to recognize the date column as a date, but it treats the column as a string, probably because it was converted implicitly in a date format different from the one set in your regional settings.

    I think you can convert the date to string using CONVERT and specifying the date format recognized by Excel.

    For instance, for the Italian date format, you can use:

    SELECT CONVERT(varchar(30), getdate(), 105) -- 27-05-2011

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • May 27, 2011 at 10:54 am

    #1331124

    All right.

    Let me check this on monday !

    Have a nice we,

    Grazie !

Viewing 7 posts - 1 through 6 (of 6 total)

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