help with datediff

Question

Post reply

help with datediff

avishain

Mr or Mrs. 500

Points: 506
More actions
June 24, 2013 at 6:20 am

#276640

hello there
got 2 time fields
*start date
*end date
the format of these columns is hhmmss
i 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 001612
so if you subtract ( 001612-235641) you got a negative number , in that case, i think you need to add 24 hours...
thank you

Viewing 13 posts - 1 through 12 (of 12 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

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 SSC Guru Points: 186127 More actions · Answer 2

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 Shaw}

For 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 Moden

avishain Mr or Mrs. 500 Points: 506 More actions · Answer 3

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 time

I DO HAVE ANOTHER COLUMNS

Date_StartHour_StartDate_EndHour_End

2011062110423220110621121805

as you can see, i got 2 columns referring to the date and 2 columns referring to the time (exact hour)

the date format is yyyymmdd

the time format is hhmmss

thank you for helping 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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 time
I DO HAVE ANOTHER COLUMNS
Date_StartHour_StartDate_EndHour_End
2011062110423220110621121805
as you can see, i got 2 columns referring to the date and 2 columns referring to the time (exact hour)
the date format is yyyymmdd
the time format is hhmmss
thank 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 Shaw}

For 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 Moden

avishain Mr or Mrs. 500 Points: 506 More actions · Answer 5

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 time
I DO HAVE ANOTHER COLUMNS
Date_StartHour_StartDate_EndHour_End
2011062110423220110621121805
as you can see, i got 2 columns referring to the date and 2 columns referring to the time (exact hour)
the date format is yyyymmdd
the time format is hhmmss
thank you for helping 🙂
What datatype are the columns?

varchar (8) (date) and varchar(6) (hour)

David McKinney SSChampion Points: 10472 More actions · Answer 6

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 SSChampion Points: 10472 More actions · Answer 7

....something like....

cast(date_start + left(hour_start,2)+':'+substring (hour_start,3,2)+':'+right(hour_start,2) as datetime)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

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 jh

CROSS 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 Shaw}

For 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 Moden

avishain Mr or Mrs. 500 Points: 506 More actions · Answer 9

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 jh
CROSS 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

avishain Mr or Mrs. 500 Points: 506 More actions · Answer 10

avishain (6/25/2013)
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 jh
CROSS 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

while trying the other 2 fields i get this error:(i got 2 fields of start and 2 fields of end and they got the same data type as i mentioned earlier)

Msg 242, Level 16, State 3, Line 1

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

David McKinney SSChampion Points: 10472 More actions · Answer 11

Sounds like a data problem - you'll have to find the problems in your data first, and then decide how to deal with them.

Suggestions

1) Run your script without the 'cast to datetime' i.e. keeping the result as a varchar. Then examine your results, and look for the one(s) that doesn't look right.

2) Have you any nulls in either field?

3) check the len(x) of the fields and look for misfits.

Once you've found the problem data, then you need to judge whether it's enough to correct it, or whether more such data can find it's way in to the DB, in which case you'll have to 'handle it' more gracefully.

avishain Mr or Mrs. 500 Points: 506 More actions · Answer 12

Ok after a couple of hours i figures what the problem is.

when the time (hours) is '240000' the query failed.

so i need to replace '240000' with '000000' and everything is fine...