SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help with datediff


help with datediff

Author
Message
avishain
avishain
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 115
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
J Livingston SQL
J Livingston SQL
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12449 Visits: 37638
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
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42380 Visits: 20012
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
Exploring Recursive CTEs by Example Dwain Camps
avishain
avishain
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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 time




I DO HAVE ANOTHER COLUMNS

Date_Start Hour_Start Date_End Hour_End
20110621 104232 20110621 121805

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 Smile
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42380 Visits: 20012
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_Start Hour_Start Date_End Hour_End
20110621 104232 20110621 121805

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 Smile


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
Exploring Recursive CTEs by Example Dwain Camps
avishain
avishain
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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 time




I DO HAVE ANOTHER COLUMNS

Date_Start Hour_Start Date_End Hour_End
20110621 104232 20110621 121805

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 Smile


What datatype are the columns?


varchar (8) (date) and varchar(6) (hour)
David McKinney
David McKinney
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2823 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
David McKinney
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2823 Visits: 2090
....something like....

cast(date_start + left(hour_start,2)+':'+substring (hour_start,3,2)+':'+right(hour_start,2) as datetime)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42380 Visits: 20012
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
Exploring Recursive CTEs by Example Dwain Camps
avishain
avishain
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 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 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search