May 29, 2015 at 7:03 am
Hello friends,
how can I get time difference of the following record please help,
STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM
I have tried it by below query,
SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER
but it gives following error message
Operand data type time is invalid for subtract operator.
May 29, 2015 at 7:20 am
When you want to do math on date or datetime you should use DATEADD. When you want to check the difference between two date or datetimes you should use DATEDIFF. You would do yourself a huge favor by using the time datatype instead of storing time information in a varchar column.
with something as
(
select '3:30 PM' as StartTime
, '4:30 PM' as EndTime
UNION ALL
select '7:30 PM', '8:30 PM'
)
select DATEDIFF(minute, CONVERT(TIME,STARTTIME,108) , CONVERT(TIME,ENDTIME,108))
from something
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2015 at 7:48 am
thanks, I have just made slite change in that, in place of minute, I take hour.
May 29, 2015 at 8:01 am
pspkshah (5/29/2015)
thanks, I have just made slite change in that, in place of minute, I take hour.
Be careful using hour. If the start minute is 59 and the end minute is 00 it will return 1. Also, what do you want to happen if the endtime is the next day?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2015 at 8:35 am
pspkshah (5/29/2015)
Hello friends,how can I get time difference of the following record please help,
STARTTIME ENDTIME
3:30 PM 4:30PM
7:30 PM 8:30PM
I have tried it by below query,
SELECT CONVERT(TIME,STARTTIME,108) - CONVERT(TIME,ENDTIME,108) FROM BATCH_MASTER
but it gives following error message
Operand data type time is invalid for subtract operator.
''
Are there date columns that go with each of these times? I ask because if someone has a start time of, say, 11:00 PM and an end time of 06:00 AM the next day, you're going to run into a wee bit of a problem. There's a work around but having date associated with each time would make this a whole lot easier.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 30, 2015 at 6:05 am
no jeff there is no such col
these two col are having char(8) datatype only....
May 30, 2015 at 11:47 am
pspkshah (5/29/2015)
thanks, I have just made slite change in that, in place of minute, I take hour.
You really do need to heed Sean's warning on this. Do the DATEDIFF as minutes and divide the result by 60.0 to get hours.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply