Get Minute difference from two SQL columns

Question

I have two columns in a table: 1st column only stores hhmm (Ex. 2350 aka 11:50pm) in varchar(257) format. 2nd column is the timestamp.

How do I get the minute difference between the two?

Here's the example of two columns. When in Management Studio, I click Alt + F1 to view info about the table, I am told Column 1 is varchar type and Column 2 is Datetime type:

First column holds 4 characters in a varchar(257)? What is the point of the second column? What is the datatype of the second column? Why are you not storing date information in a datetime datatype instead of characters? What have you tried? Have you heard of DATEDIFF? — Sean Lange, Aug 05 '15 at 19:23
What have you tried? Can you provide sample data? What do you want the output to look like? — TTeeple, Aug 05 '15 at 19:24
The second column is datetime type. It has date and time in following format: 2015-08-01 07:14:00.000. The first column simply lists 0714 (hhmm). I am having trouble getting the difference between them in minutes. So far here is what I tried: SUBSTRING (FirstColumn, 1, 2) + ':' + SUBSTRING (FirstColumn, 3, 2) as FirstColumn, CONVERT(VARCHAR(5),SecondColumn,108) AS SecondColumn. I am having a hard time subtracting them both from each others due to the type. — NonProgrammer, Aug 07 '15 at 13:36
The datetime datatype is not stored in a format. It is stored as a value, the only time the format comes into play is when you are looking at the return. This really isn't that hard but I need some details to work with. Can you provide a couple of rows of data and what the expected output should be for those? — Sean Lange, Aug 07 '15 at 13:59

score 1 · Accepted Answer · answered Aug 10 '15 at 13:56

This would have been a lot quicker and easier if you had provided this as consumable data. I did not bother typing all your sample data. The challenge here is that the data is improperly stored. I realize this is outside your control but you still have to force this back into a usable datatype. You will need to add some validation to this to ensure that the values are valid times. This also doesn't handle crossing day boundaries but given the nature of the data I don't know how you could even pretend to deal with that well.

create table #Something
(
    Col1 varchar(10)
    , Col2 datetime
)

insert #Something
select '1230', '2014-12-19 13:09:00.000' union all
select '1350', '2015-08-10 14:49:00.000'

select Col1
    , Col2
    , DATEDIFF(minute, cast(STUFF(Col1, 3, 0, ':') as time), CAST(Col2 as time))
from #Something

Get Minute difference from two SQL columns

1 Answers1