Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 help with datediff Rate Topic Display Mode Topic Options
Author
 Message
 Posted Monday, June 24, 2013 6:20 AM
 Grasshopper Group: General Forum Members Last Login: Monday, September 1, 2014 12:55 AM Points: 22, 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
Post #1466709
 Posted Monday, June 24, 2013 6:26 AM
 Hall of Fame Group: General Forum Members Last Login: 2 days ago @ 3:26 PM Points: 3,318, Visits: 32,627
 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
Post #1466711
 Posted Monday, June 24, 2013 6:34 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 10:32 AM Points: 8,595, Visits: 18,764
 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
Post #1466715
 Posted Monday, June 24, 2013 7:13 AM
 Grasshopper Group: General Forum Members Last Login: Monday, September 1, 2014 12:55 AM Points: 22, 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 :)
Post #1466728
 Posted Monday, June 24, 2013 7:30 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 10:32 AM Points: 8,595, Visits: 18,764
 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
Post #1466735
 Posted Monday, June 24, 2013 7:38 AM
 Grasshopper Group: General Forum Members Last Login: Monday, September 1, 2014 12:55 AM Points: 22, 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)
Post #1466737
 Posted Monday, June 24, 2013 8:27 AM
 Say Hey Kid Group: General Forum Members Last Login: Tuesday, September 13, 2016 8:51 AM Points: 695, Visits: 2,090
 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)
Post #1466768
 Posted Monday, June 24, 2013 8:28 AM
 Say Hey Kid Group: General Forum Members Last Login: Tuesday, September 13, 2016 8:51 AM Points: 695, Visits: 2,090
 ....something like....cast(date_start + left(hour_start,2)+':'+substring (hour_start,3,2)+':'+right(hour_start,2) as datetime)
Post #1466769
 Posted Monday, June 24, 2013 8:43 AM
 SSCrazy Eights Group: General Forum Members Last Login: Today @ 10:32 AM Points: 8,595, Visits: 18,764
 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
Post #1466771
 Posted Tuesday, June 25, 2013 12:34 AM
 Grasshopper Group: General Forum Members Last Login: Monday, September 1, 2014 12:55 AM Points: 22, 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
Post #1466994

 Permissions