Post reply

Set time format in create table

  • December 4, 2015 at 6:42 am

    #399423

    Hi,

    I need to import data from excel to a sql table and one of the column in excel has time values of the format - hh:mm:ss.

    The problem is the hours value can exceed 24 and if I assign the 'time' datatype to the associated column in sql, it does not store values exceeding 24 hours. For e.g. if the value in excel is 25:10:20, then it stores the value as 01:10:20 in the sql table.

    Does somebody know how to overcome this issue ?

    Is it possible to convert the date time format in the create table but also include hours values exceeding 24 ?

    Thanks.

  • December 4, 2015 at 6:47 am

    #1844051

    The TIME data type stores a time of day, not a time interval. Since a day has only 24 hours, that's the max that TIME can store (well, 23:59:59.9999...).

    Time intervals are usually best stored in integer columns as a number of time units, eg seconds, minutes or hours, and converted to a string format on display. I'd suggest import it into a varchar column and convert after that. Since it appears that you need resolution to seconds, your 25 hours, 10 minutes and 20 seconds could be stored as 90620 with the column name or documentation indicating that it's a number of seconds.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 4, 2015 at 6:58 am

    #1844054

    GilaMonster (12/4/2015)

    The TIME data type stores a time of day, not a time interval. Since a day has only 24 hours, that's the max that TIME can store (well, 23:59:59.9999...).

    Time intervals are usually best stored in integer columns as a number of time units, eg seconds, minutes or hours, and converted to a string format on display. I'd suggest import it into a varchar column and convert after that. Since it appears that you need resolution to seconds, your 25 hours, 10 minutes and 20 seconds could be stored as 90620 with the column name or documentation indicating that it's a number of seconds.

    Thanks very much Gail.

    Could you please advise on how to convert it to seconds after it has been stored in the varchar column ?I mean it would be stored as 25:10:20 in the sql column and if I then want to convert it to seconds in reporting services, how do I do it because I would also need to handle the special character ':'.

    Thanks again.

  • December 4, 2015 at 7:03 am

    #1844057

    Use LEFT, SUBSTRING and RIGHT along with CHARINDEX to locate the :. Break it into the hours, minutes and seconds, then convert each fragment to int and multiple by 60 or 3600 as necessary to get the total number of seconds.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 4, 2015 at 8:02 am

    #1844075

    Here are a couple of examples but I highly recommend you read up on string functions.

    https://msdn.microsoft.com/en-us/library/ms181984.aspx

    DECLARE @time VARCHAR(10) = '25:10:20'

    --Lazy way & will break if pattern is different

    SELECT

    CAST(SUBSTRING(@time,1,2) AS INT) * 3600 + CAST(SUBSTRING(@time,4,2) AS INT) * 60 + CAST(SUBSTRING(@time,7,2) AS INT) AS Seconds

    --This way will support more than 99 hours but is still a problem if you have decimal in seconds.

    SELECT

    (CAST(REPLACE(@time,':','') AS INT) / 10000 * 3600) + (CAST(REPLACE(@time,':','') AS INT) / 100 % 100 * 60) + (CAST(REPLACE(@time,':','') AS INT) % 100)

    SET @time = '125:10:20'

    SELECT

    (CAST(REPLACE(@time,':','') AS INT) / 10000 * 3600) + (CAST(REPLACE(@time,':','') AS INT) / 100 % 100 * 60) + (CAST(REPLACE(@time,':','') AS INT) % 100)

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • December 4, 2015 at 8:24 am

    #1844082

    pwalter83 (12/4/2015)

    GilaMonster (12/4/2015)

    The TIME data type stores a time of day, not a time interval. Since a day has only 24 hours, that's the max that TIME can store (well, 23:59:59.9999...).

    Time intervals are usually best stored in integer columns as a number of time units, eg seconds, minutes or hours, and converted to a string format on display. I'd suggest import it into a varchar column and convert after that. Since it appears that you need resolution to seconds, your 25 hours, 10 minutes and 20 seconds could be stored as 90620 with the column name or documentation indicating that it's a number of seconds.

    Thanks very much Gail.

    Could you please advise on how to convert it to seconds after it has been stored in the varchar column ?I mean it would be stored as 25:10:20 in the sql column and if I then want to convert it to seconds in reporting services, how do I do it because I would also need to handle the special character ':'.

    Thanks again.

    Thanks again Gail.

    However, I am guessing there would be issues if I assign the varchar datatype to this column. The reason is this column would be used as a parameter in reporting services with calendar selection to select the dates. How will this work in that case ?

    Is there no way to define the column in the create table as dd hh:mm:ss so that when the data is imported from excel to sql, the value 25:10:20 is changed to 01 01:10:20 ?

    Thanks.

  • December 4, 2015 at 9:28 am

    #1844101

    pwalter83 (12/4/2015)

    pwalter83 (12/4/2015)

    GilaMonster (12/4/2015)

    The TIME data type stores a time of day, not a time interval. Since a day has only 24 hours, that's the max that TIME can store (well, 23:59:59.9999...).

    Time intervals are usually best stored in integer columns as a number of time units, eg seconds, minutes or hours, and converted to a string format on display. I'd suggest import it into a varchar column and convert after that. Since it appears that you need resolution to seconds, your 25 hours, 10 minutes and 20 seconds could be stored as 90620 with the column name or documentation indicating that it's a number of seconds.

    Thanks very much Gail.

    Could you please advise on how to convert it to seconds after it has been stored in the varchar column ?I mean it would be stored as 25:10:20 in the sql column and if I then want to convert it to seconds in reporting services, how do I do it because I would also need to handle the special character ':'.

    Thanks again.

    Thanks again Gail.

    However, I am guessing there would be issues if I assign the varchar datatype to this column. The reason is this column would be used as a parameter in reporting services with calendar selection to select the dates. How will this work in that case ?

    Is there no way to define the column in the create table as dd hh:mm:ss so that when the data is imported from excel to sql, the value 25:10:20 is changed to 01 01:10:20 ?

    Thanks.

    There is no data type for what you are describing. Gail has already explained to you regarding dates vs time intervals. Storing seconds allows you to use that to do other date calculations.

    Simple example:

    DECLARE @seconds INT = 90620

    SELECT GETDATE() AS RightNow, DATEADD(ss, @seconds, GETDATE()) AS x_seconds_from_now

    If someone were to select 2 dates from a calendar to generate a report how would a time interval alone help you?

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

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

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