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

Question

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

Viewing 13 posts - 1 through 12 (of 12 total)

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

sgmunson SSC Guru Points: 110640 More actions · Answer 1

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: 101106 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Raynard_SwanXI SSC Veteran Points: 296 More actions · Answer 3

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: 99130 More actions · Answer 4

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 · Answer 5

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: 99130 More actions · Answer 6

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 · Answer 7

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: 110640 More actions · Answer 8

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 · Answer 9

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: 99130 More actions · Answer 10

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 · Answer 11

Raynard_SwanXI

SSC Veteran

Points: 296

October 2, 2018 at 9:20 am

#2007730

Oo I see, thanks!

frederico_fonseca SSCoach Points: 16157 More actions · Answer 12

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.