## help with datediff

 Author Message avishain SSC Rookie Group: General Forum Members Points: 28 Visits: 115 hello theregot 2 time fields *start date*end datethe format of these columns is hhmmssi want to calculate the duration . (end date-start date)could i do this with datdiff or any other function?pay attention that a situation like this can be happened :*start date 235641*end date 001612so if you subtract ( 001612-235641) you got a negative number , in that case, i think you need to add 24 hours...thank you J Livingston SQL SSCertifiable Group: General Forum Members Points: 5501 Visits: 35453 do you have any other columns available that hold date info ...eg yyymmdd?also what are you expecting to happen when the end time is two or more days after the start time ________________________________________________________________you can lead a user to data....but you cannot make them think and remember....every day is a school day ChrisM@Work SSCoach Group: General Forum Members Points: 16468 Visits: 19557 While you're investigating JLS' questions, this is worth playing with:`SELECT [DTstart date], [DTend date], SecondsDifference = DATEDIFF(second,[DTend date],[DTstart date]), SecondsDifference = ABS(DATEDIFF(second,[DTstart date],[DTend date]))FROM ( SELECT [DTstart date] = CONVERT(DATETIME,STUFF(STUFF(CAST(d.[start date] AS VARCHAR(8)),3,0,':'),6,0,':'),114), [DTend date] = CONVERT(DATETIME,STUFF(STUFF(CAST(d.[end date] AS VARCHAR(8)),3,0,':'),6,0,':'),114) FROM ( SELECT [start date] = '235641', [end date] = '001612' ) d) e` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps avishain SSC Rookie Group: General Forum Members Points: 28 Visits: 115 J Livingston SQL (6/24/2013)do you have any other columns available that hold date info ...eg yyymmdd?also what are you expecting to happen when the end time is two or more days after the start timeI DO HAVE ANOTHER COLUMNS Date_Start Hour_Start Date_End Hour_End20110621 104232 20110621 121805as you can see, i got 2 columns referring to the date and 2 columns referring to the time (exact hour)the date format is yyyymmddthe time format is hhmmssthank you for helping ChrisM@Work SSCoach Group: General Forum Members Points: 16468 Visits: 19557 avishain (6/24/2013)J Livingston SQL (6/24/2013)do you have any other columns available that hold date info ...eg yyymmdd?also what are you expecting to happen when the end time is two or more days after the start timeI DO HAVE ANOTHER COLUMNS Date_Start Hour_Start Date_End Hour_End20110621 104232 20110621 121805as you can see, i got 2 columns referring to the date and 2 columns referring to the time (exact hour)the date format is yyyymmddthe time format is hhmmssthank you for helping What datatype are the columns? “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps avishain SSC Rookie Group: General Forum Members Points: 28 Visits: 115 ChrisM@Work (6/24/2013)avishain (6/24/2013)J Livingston SQL (6/24/2013)do you have any other columns available that hold date info ...eg yyymmdd?also what are you expecting to happen when the end time is two or more days after the start timeI DO HAVE ANOTHER COLUMNS Date_Start Hour_Start Date_End Hour_End20110621 104232 20110621 121805as you can see, i got 2 columns referring to the date and 2 columns referring to the time (exact hour)the date format is yyyymmddthe time format is hhmmssthank you for helping What datatype are the columns?varchar (8) (date) and varchar(6) (hour) David McKinney Ten Centuries Group: General Forum Members Points: 1325 Visits: 2090 You're probably going to have to cut up the time and insert colons, using the substring (and if you like, left and right) functions, so that you end up with something you can cast to a date, at which point you can use the datediff function as intended.DECLARE @mydate VARCHAR(40)SELECT @mydate='20110621 10:42:32'SELECT CAST(@mydate AS DATETIME) David McKinney Ten Centuries Group: General Forum Members Points: 1325 Visits: 2090 ....something like....cast(date_start + left(hour_start,2)+':'+substring (hour_start,3,2)+':'+right(hour_start,2) as datetime) ChrisM@Work SSCoach Group: General Forum Members Points: 16468 Visits: 19557 Something like the algorithms for obtaining datetime from sysjobhistory run date and run time:`SELECT jh.run_date, jh.run_time, RunDateTime = x.RunDate + x.RunTime FROM msdb.dbo.sysjobhistory jhCROSS APPLY ( SELECT RunDate = CAST(STR(jh.run_date, 8, 0) AS DATETIME), RunTime = CAST(STUFF(STUFF(RIGHT('00000'+CAST(jh.run_time AS VARCHAR(8)),6),3,0,':'),6,0,':') AS DATETIME)) x` “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.Understanding and using APPLY, (I) and (II) Paul White Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff ModenExploring Recursive CTEs by Example Dwain Camps avishain SSC Rookie Group: General Forum Members Points: 28 Visits: 115 ChrisM@Work (6/24/2013)Something like the algorithms for obtaining datetime from sysjobhistory run date and run time:`SELECT jh.run_date, jh.run_time, RunDateTime = x.RunDate + x.RunTime FROM msdb.dbo.sysjobhistory jhCROSS APPLY ( SELECT RunDate = CAST(STR(jh.run_date, 8, 0) AS DATETIME), RunTime = CAST(STUFF(STUFF(RIGHT('00000'+CAST(jh.run_time AS VARCHAR(8)),6),3,0,':'),6,0,':') AS DATETIME)) x`thank you very much works like a charm