Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

help with datediff Expand / Collapse
Author
Message
Posted Monday, June 24, 2013 6:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 1, 2014 12:55 AM
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

Post #1466709
Posted Monday, June 24, 2013 6:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 1,917, Visits: 19,593
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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 6,782, Visits: 13,988
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
Post #1466715
Posted Monday, June 24, 2013 7:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 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 :)
Post #1466728
Posted Monday, June 24, 2013 7:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 6,782, Visits: 13,988
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 :)


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
Post #1466735
Posted Monday, June 24, 2013 7:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 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 :)


What datatype are the columns?


varchar (8) (date) and varchar(6) (hour)
Post #1466737
Posted Monday, June 24, 2013 8:27 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:54 AM
Points: 648, Visits: 1,876
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
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, October 17, 2014 12:54 AM
Points: 648, Visits: 1,876
....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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 6,782, Visits: 13,988
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
Post #1466771
Posted Tuesday, June 25, 2013 12:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 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
Post #1466994
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse