March 11, 2009 at 10:55 pm
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 ?
March 12, 2009 at 3:20 am
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
March 12, 2009 at 6:07 am
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.
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]
March 12, 2009 at 6:48 am
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 ?)
March 12, 2009 at 7:16 am
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.
March 12, 2009 at 7:28 am
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
March 12, 2009 at 7:32 am
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.
March 12, 2009 at 8:37 am
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
March 12, 2009 at 10:33 am
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?
March 12, 2009 at 10:37 am
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
March 13, 2009 at 12:55 am
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.
March 13, 2009 at 1:36 am
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/
March 14, 2009 at 1:36 am
I got the above msg when i tried to run the queries offered as solutions (to my time difference calculation prob. )here .
March 14, 2009 at 10:59 am
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
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy