Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


help with datediff


help with datediff

Author
Message
avishain
avishain
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3448 Visits: 33039
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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8973 Visits: 19024
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
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
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 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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8973 Visits: 19024
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
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
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 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
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 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
Right there with Babe
Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)Right there with Babe (769 reputation)

Group: General Forum Members
Points: 769 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
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8973 Visits: 19024
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
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

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