Post reply

Importing hours mins seconds into SQL Server

  • September 22, 2004 at 3:30 am

    #162218

    Hi, I want to import data into SQL Server from an Excel spreadsheet.

    A number of the columns in the spreadsheet relate to elapsed time in hours, minutes and seconds. So 00:03:15 means the elapsed time was 3 minutes 15 seonds not that the time was 3 minutes 15 seconds past midnight.

    After I have imported data into SQL Server I need to be able to use these columns for calculations and therefore they require a data type that recognises that they represent elapsed time. I tried setting-up a User Defined Data Type but that did nothing more than setting the column to a datetime type.

    Any ideas?

    Thanks.

    Duncan

     

    All the best,

    Duncan

  • September 22, 2004 at 5:41 am

    #523951

    Unfortunately, Time is not a separate entity yet in SQL.  IF you use: '1900-01-01' + SPACE(1) + CONVERT(VARCHAR(10), GETDATE(), 108) you may be able to then use DATEDIFF with MINUTES/SECONDS if you need to do comparisons.

    OR use '12-31-9999 ' and the time.

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • September 23, 2004 at 7:07 am

    #524154

    Thanks for your response.

    Thinking about it some more, I'm going to convert the values into minutes and hold the elapsed time as integer (sufficient for my purposes).

    All the best.

    Duncan

    All the best,

    Duncan

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

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