time difference computation

Question

time difference computation

sqlnewbie007

Valued Member

Points: 66
More actions
March 11, 2009 at 10:55 pm

#197999

I have 2 columns - start time and end time expressed as hh:mm. What query will insert a column in the table which gives the difference between end time and start time in minutes ? As what data type do i need to enter start time and end time ?

Viewing 14 posts - 1 through 14 (of 14 total)

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

karthik M One Orange Chip Points: 29139 More actions · Answer 1

thomyes,

if you need help from here, post the table structure with sample data. I am sure you will get some good replies from this forum.

are you going to store time alone or data and time?

if you are going to store date and time use 'DATETIME' data type.

if you are going to store hh:mm only then use 'VARCHAR' data type.

karthik

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 2

Something like this?

[font="Courier New"]CREATE TABLE #Temp (Col1 DATETIME, Col2 DATETIME)

INSERT INTO #Temp VALUES ('0:10:00', '3:15:00')

INSERT INTO #Temp VALUES ('0:10:00', '0:15:00')

INSERT INTO #Temp VALUES ('0:10:00', '1:15:00')

SELECT CONVERT(VARCHAR(8),Col1,108) AS StartTime,

CONVERT(VARCHAR(8),Col2,108) AS EndTime,

(DATEPART(hour,CONVERT(DATETIME,Col2-Col1,108)) * 60) + DATEPART(MINUTE,CONVERT(DATETIME,Col2-Col1,108)) AS TimeDiff

FROM #Temp

DROP TABLE #Temp

[/font]

Thanks.

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].

sqlnewbie007 Valued Member Points: 66 More actions · Answer 3

Thnx Karthik

Data is as below

ID Startday Starttime Endday Endtime

1 1 21:10 1 21:50

2 1 22:48 1 23:15

3 1 23:50 2 00:15

Start time and end time given in hh:min (no date), Pls note that for 3rd data point enday >startday

what query will compute the time elapsed and insert it into the this table which already has the data (computed column ?)

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 4

are you going to store time alone or data and time?
if you are going to store date and time use 'DATETIME' data type.
if you are going to store hh:mm only then use 'VARCHAR' data type.

I disagree with this. For storing times, I would either use a datetime datatype where the date portion is zero ('1900-01-01') or possibly in certain circumstances an integer or floating point data type where the value represents the number of seconds (or milliseconds or minutes) since midnight. I can't think of a situation where I would choose to store the time as a varchar.

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 5

Assuming the data types are like my @testData table below:

DECLARE @testData TABLE (

ID int,

Startday int,

Starttime varchar(5),

Endday int,

Endtime varchar(5)

)

/* Test data */

INSERT @testData (ID, Startday, Starttime, Endday, Endtime)

SELECT 1, 1, '21:10', 1, '21:50' UNION ALL

SELECT 2, 1, '22:48', 1, '23:15' UNION ALL

SELECT 3, 1, '23:50', 2, '00:15'

Then this expression will give the time difference in minutes that you require.

SELECT DATEDIFF(minute,

DATEADD(day, Startday, Starttime),

DATEADD(day, Endday, Endtime)) AS ElapsedTime

FROM @testData

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

How are you looking for elapsed time to be returned? Not knowing your data, I don't know if I need to worry about elapsed time going over 24 hours and if it does do I return elpased time only as hours and minutes or do we add days to the mix.

GSquared SSC Guru Points: 260824 More actions · Answer 7

Another possible solution:

create table #T (

ID int primary key,

StartDay int,

StartTime char(5),

EndDay int,

EndTime char(5));

insert into #T (ID, StartDay, StartTime, EndDay, EndTime)

select 1,1,'21:10',1,'21:50' union all

select 2,1,'22:48',1,'23:15' union all

select 3,1,'23:50',2,'00:15';

select

datediff(

minute,

cast(starttime as datetime),

dateadd(day, endday - startday, cast(endtime as datetime)))

from #T;

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ron McCullough SSC Guru Points: 63877 More actions · Answer 8

Thomyes

A question - your start and end day values suspiciously look as if they are designating a day of the week.. that is start day 1 is a Monday, 2 - Tuesday etc. If you move then from one week to the next week would it be possible then that a start day of 5 (Friday) and the day ended is let us say Monday (day 1) of the following week?

Or am I making a mountain out of a mole hill?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 9

It would be much simpler if you just stored that actual start and end times as datetime datatypes as the actual start datetime and end datetime. Then is is very simple to get the elapsed time: endtime-starttime

sqlnewbie007 Valued Member Points: 66 More actions · Answer 10

Hi,

im getting the flwng msg:

Server: Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 11

The error message is very descriptive – you are trying to convert a string to date but the value in the string can not be converted to date. One reason could be that your string has a date in it, but in a format that SQL Server doesn’t understand. For example take a look at this string – ‘12/04/1968’. This string can be April 12th or December 4th. Without specifying the format you can’t know for sure what date was written. If in your code you don’t specify the format that you are using, the server tries to convert it to date using the default format. If the default format is mm/dd/yyyy, the server will convert my exemple to December 12th. Suppose that the string will have the value of ‘13/04/1968’, you wanted to work with the ‘dd/mm/yyyy format, but you didn’t specify it and the default date format is ‘mm/dd/yyyy’. In this case you will get an error message saying that it can not be converted to datetime data type. When ever you convert a string into a date, you have to make sure that the string can be converted into the date and that the server will be using the same format that you are using. There are few ways of doing so. You can do it by issuing set dateformat statement XXX before converting the string (Instead of XXX you have to specify the date format). Another option is to use the third parameter in the convert function which tells the server what format you are using (You can look for the list of formats that the server supports in BOL under the explanation about cast and convert functions). One more option is to use a format that will always be uderstood in one way regardless of the server/session/user configuration. One format that is ‘yyyymmdd’.

Adi

edit - Just noticed that this is an on going thread. I got to this tread from the homepage and didn't notice that I'm on page 2 and not on page 1. When I wrote the message it seemed that the only question in the thread was about the error message.

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sqlnewbie007 Valued Member Points: 66 More actions · Answer 12

I got the above msg when i tried to run the queries offered as solutions (to my time difference calculation prob. )here .

Jeff Moden SSC Guru Points: 1004600 More actions · Answer 13

thomyes (3/14/2009)
I got the above msg when i tried to run the queries offered as solutions (to my time difference calculation prob. )here .

It simply means that you have data in a column that cannot be converted to a datetime datatype. Use ISDATE() to find out where the data "corruption" exists in your data. I'm with the others on properly storing times as the datatime datatype but, once you have fixed the "corruption", you might want to add a constraint to the column using ISDATE() to validate all new data added.

Keep in mind that ISDATE() is not the panacea it would appear to be... some whole numbers will cause ISDATE() to return a "1". You may have to add a patterned LIKE, as well.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

time difference computation

Cookies on SQLServerCentral