Format column as HHH:MM:SS instead of MMM DD YYYY HH:MM (AM/PM)

• Raynard_SwanXI

  SSC Veteran

  Points: 296

  More actions

  October 1, 2018 at 9:27 am

  #377841

  Hi everyone,

  This problem seemingly does not have a clear solution.

  I am importing data from Excel using OPENROWSET. In the Excel files I have columns that contain time durations in the format of "HHH:MM:SS" e.g. 741:51:59 (741 hours, 51 minutes and 59 seconds). 

  When I import this value into a SQL Server  VARCHAR column, the number is converted to the following format MMM DD YYYY HH:MM (AM/PM)  e.g. Jan 30 1900 11:34PM.

  Is it possible to define the SQL column to handle this format, or any other suggestions to solve this?

  Thank you.

  Raynard

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  October 1, 2018 at 1:01 pm

  #2007581

  I'm doubtful this could work.   Excel picks a data type and then you are stuck with it.   This would be much easier to handle with T-SQL after exporting the Excel file to CSV, after forcing the data in that particular column to be text.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• ScottPletcher

  SSC Guru

  Points: 100949

  More actions

  October 1, 2018 at 2:04 pm

  #2007596

  If those values truly correspond with your results:
  741:51:59 = Jan 30 1900 11:34PM
  then I'm not sure what you can, as I don't see a clear link between the two values.

  If, however: 
  741:51:59 = Jan 30 1900 21:51:59PM (or 21:51/21:52 for time only to the minute)
  then you could add a computed column to the column that would format the data as you originally had it (albeit in char form, not in a numeric time_duration form, which, afaik, SQL Server doesn't have yet).

  
IF OBJECT_ID('tempdb.dbo.#time_duration') IS NOT NULL DROP TABLE #time_duration;

  CREATE TABLE #time_duration (
  time_datetime datetime NULL,
  time_duration AS CAST(CAST(DATEDIFF(HOUR, 0, time_datetime) + 24 AS varchar(10)) + ':' +
   SUBSTRING(CONVERT(varchar(8), CAST(time_datetime AS time), 8), 4, 5) AS varchar(16))
  );

  INSERT INTO #time_duration
SELECT 'Jan 30 1900 21:51:59'

  SELECT * FROM #time_duration

  SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

• Raynard_SwanXI

  SSC Veteran

  Points: 296

  More actions

  October 2, 2018 at 1:11 am

  #2007635

  Hi Steve, unfortunately I can not change the format manually as there are hundreds of these sheets sent each month. The goal is to automate the process as much as possible.

  Hi Scott, thanks for the work around. The two values I provided previously are 1-to-1 comparisons. Will your solution not work in this case?

  Otherwise I will try and work this into my solution. Its unfortunate that SQL Server does not support this format, would have thought there are more people in need of this functionality.

  Just to make sure, casting the date time to HOUR does support hours higher than 24?

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 2, 2018 at 5:16 am

  #2007650

  Could we have some more examples? Perhaps we can spot a pattern then. What is the size of your varchar column as well, perhaps it's suffering some truncation?

  But I agree with Scott, I can't see how the value '741:51:59' becomes 'Jan 30 1900 11:34PM'.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Raynard_SwanXI

  SSC Veteran

  Points: 296

  More actions

  October 2, 2018 at 6:49 am

  #2007663

  Hi Thom,

  The column was initially varchar(50), but I changed it now to datetime. Some examples of the data:

  Excel        = SQL Server
  744:00:00 = 1900-01-31 00:00:00.000
  741:57:42 = 1900-01-30 21:57:41.997
  743:53:27 = 1900-01-30 23:53:26.570

  I think it is correct now?

  Regards,
  Raynard

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 2, 2018 at 7:13 am

  #2007672

  Well, those are more like the values I expected. This is a little messy, but with the values you have supplied get's you the the correct results. Note that the conversion to a datetime2(0) in the VTE. This is important as it'll "round up" the milliseconds in your data.

  WITH VTE AS(

    SELECT CONVERT(datetime2(0), SQLTime) AS SQLTime

    FROM (VALUES ('1900-01-31T00:00:00.000'),

         ('1900-01-30T21:57:41.997'),

         ('1900-01-30T23:53:26.570')) V(SQLTime))

  SELECT CONVERT(varchar(4),H.T) + ':' + RIGHT('0' + CONVERT(varchar(2),M.T),2) + ':' + RIGHT('0' + CONVERT(varchar(2),S.T),2)

  FROM VTE

    CROSS APPLY(VALUES(DATEDIFF(HOUR,'18991231',SQLTime))) H(T)

    CROSS APPLY(VALUES(DATEDIFF(MINUTE,'18991231',DATEADD(HOUR,-H.T,SQLTime)))) M(T)

    CROSS APPLY(VALUES(DATEDIFF(SECOND,'18991231',DATEADD(MINUTE,-M.T,DATEADD(HOUR,-H.T,SQlTime))))) S(T);

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Raynard_SwanXI

  SSC Veteran

  Points: 296

  More actions

  October 2, 2018 at 7:32 am

  #2007683

  Thanks, I replaced the VALUES with a SELECT statement on my column that contains the original data. 

  The output is a column with all the converted values. I will work on how to use this in my final solution. (After changing my import column to datetime Scott's solution does also work!)

  BTW why the added T between the date and time values?

  Thanks for everyone's help!

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  October 2, 2018 at 8:53 am

  #2007723

  Raynard_SwanXI - Tuesday, October 2, 2018 7:32 AM

  Thanks, I replaced the VALUES with a SELECT statement on my column that contains the original data. 

  The output is a column with all the converted values. I will work on how to use this in my final solution. (After changing my import column to datetime Scott's solution does also work!)

  BTW why the added T between the date and time values?

  Thanks for everyone's help!

  Just an fyi, as I suspect you now know this, but Excel can't process time values at the millisecond level.   It always rounds to the nearest second, so be aware that you are going to lose that level of precision in exporting such data from the SQL Server to Excel.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Raynard_SwanXI

  SSC Veteran

  Points: 296

  More actions

  October 2, 2018 at 9:04 am

  #2007724

  Hi Steve, thanks for the heads up, but my import is from Excel to SQL so this should not be an issue. Thanks anyway!

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 2, 2018 at 9:13 am

  #2007729

  Raynard_SwanXI - Tuesday, October 2, 2018 7:32 AM

  Thanks, I replaced the VALUES with a SELECT statement on my column that contains the original data. 

  The output is a column with all the converted values. I will work on how to use this in my final solution. (After changing my import column to datetime Scott's solution does also work!)

  BTW why the added T between the date and time values?

  Thanks for everyone's help!

  The T is because I was using a literal string for my sample dateline. The T is part is the ISO format, so it ensures that the value will be determined unambiguously.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• Raynard_SwanXI

  SSC Veteran

  Points: 296

  More actions

  October 2, 2018 at 9:20 am

  #2007730

  Oo I see, thanks!

• frederico_fonseca

  SSCoach

  Points: 16020

  More actions

  October 2, 2018 at 11:33 am

  #2007752

  Do have a look at this thread - https://www.sqlservercentral.com/Forums/1974006/convert-date-to-numeric#bm1974016 - and setup some test cases to see if your data suffers the issue mentioned on it around how Excel deals with dates.

  try with excel values of

  1392:00:00

  1416:00:00

  1440:00:00

  1464:00:00

  1488:00:00

  1512:00:00

  and see if going to SQL Server they get valid dates all of them.