Date diff with negative time

Discussion:

(too old to reply)

Dan Bridgland

2008-05-30 09:52:53 UTC

Hi,

I'm working on a time card system for employees, I'm trying to
calculate the difference between two time values, one is a datetime
column (time_store.stamp) the other is a varchar column
(employees.contracted_to_time)

If I calculate the difference in seconds minutes or hours I get an
integer value of the difference eg:
-64
-28
33
18
-27
-87

But I need to turn this value back into a format of time of Hours,
minutes and seconds (hh:mm:ss) When I try to convert negative values
appear as

-2:-4
-1:28
0:33
0:18
-1:27
-2:27

I expect to see
-0:04
-0:28
0:33
0:18
-0:27
-1:27

How can I turn the minutes value into a valid time format?

this is the code im using

(CONVERT(varchar, FLOOR((DATEDIFF(MINUTE,(MIN( CONVERT(char(12),
EMPLOYEES.CONTRACTED_TO_TIME, 108))),(MAX( CONVERT(char(12),
TIME_STORE.STAMP, 108))))) / 60.0 )) + ':'
+ RIGHT('0' + CONVERT(varchar, (DATEDIFF(MINUTE,
(MIN( CONVERT(char(12), EMPLOYEES.CONTRACTED_TO_TIME, 108))),
(MAX( CONVERT(char(12), TIME_STORE.STAMP, 108))))) % 60), 2)) AS
CONTRACTED_TO_DIFF_IN_HOURS_AND_MINUTES,

Regards
Dan

John Bell

2008-05-30 11:15:24 UTC

Permalink

Post by Dan Bridgland
Hi,
I'm working on a time card system for employees, I'm trying to
calculate the difference between two time values, one is a datetime
column (time_store.stamp) the other is a varchar column
(employees.contracted_to_time)
If I calculate the difference in seconds minutes or hours I get an
-64
-28
33
18
-27
-87
But I need to turn this value back into a format of time of Hours,
minutes and seconds (hh:mm:ss) When I try to convert negative values
appear as
-2:-4
-1:28
0:33
0:18
-1:27
-2:27
I expect to see
-0:04
-0:28
0:33
0:18
-0:27
-1:27
How can I turn the minutes value into a valid time format?
this is the code im using
(CONVERT(varchar, FLOOR((DATEDIFF(MINUTE,(MIN( CONVERT(char(12),
EMPLOYEES.CONTRACTED_TO_TIME, 108))),(MAX( CONVERT(char(12),
TIME_STORE.STAMP, 108))))) / 60.0 )) + ':'
+ RIGHT('0' + CONVERT(varchar, (DATEDIFF(MINUTE,
(MIN( CONVERT(char(12), EMPLOYEES.CONTRACTED_TO_TIME, 108))),
(MAX( CONVERT(char(12), TIME_STORE.STAMP, 108))))) % 60), 2)) AS
CONTRACTED_TO_DIFF_IN_HOURS_AND_MINUTES,
Regards
Dan

Hi Dan

Maybe something like:
DECLARE @datetime1 datetime, @datetime2 datetime

SELECT @datetime1 = '2008-05-30 11:38:02.013', @datetime2 = '2008-05-30
12:08:02.013'

SELECT
CASE WHEN @datetime1 <= @datetime2
THEN CONVERT(char(8),DATEADD(mi, DATEDIFF(mi,@datetime1, @datetime2),
0),108)
ELSE '-' + CONVERT(char(8),DATEADD(mi, DATEDIFF(mi,@datetime2, @datetime1),
0),108)
END

SELECT @datetime1 = '2008-05-30 13:38:02.013', @datetime2 = '2008-05-30
12:08:02.013'

SELECT
CASE WHEN @datetime1 <= @datetime2
THEN CONVERT(char(8),DATEADD(mi, DATEDIFF(mi,@datetime1, @datetime2),
0),108)
ELSE '-' + CONVERT(char(8),DATEADD(mi, DATEDIFF(mi,@datetime2, @datetime1),
0),108)
END

John

Dan Bridgland

2008-05-30 12:39:06 UTC

Permalink

Hi John,

Thanks for your speedy reply,

your code works a treat, thank you Very much. Though I amm having
trouble getting it to work with my columns,

When I run this code, I get the following error message
Msg 241, Level 16, State 1, Line 6
Syntax error converting datetime from character string.

DECLARE @Contracted_to datetime,
@depart datetime

SET @Contracted_to = CONVERT(char(8),'16:30:00',108)
SET @depart = CONVERT(char(8), '2008-05-30 12:08:02', 108)

SELECT
CASE WHEN @Contracted_to <= @depart
THEN CONVERT(char(8),DATEADD(ss, DATEDIFF(ss,@Contracted_to, @depart),
0),108)
ELSE '-' + CONVERT(char(8),DATEADD(ss, DATEDIFF(ss,@depart,
@Contracted_to), 0),108)
END

Once again, thanks for your assistance.

Regards
Dan

Post by John Bell

Hi Dan
12:08:02.013'
SELECT
0),108)
0),108)
END
12:08:02.013'
SELECT
0),108)
0),108)
END
John- Hide quoted text -
- Show quoted text -

John Bell

2008-05-30 13:53:02 UTC

Permalink

"Dan Bridgland" <***@gmail.com> wrote in message news:bf524e9a-ec91-4243-b252-***@8g2000hse.googlegroups.com...
Hi John,

Thanks for your speedy reply,

your code works a treat, thank you Very much. Though I amm having
trouble getting it to work with my columns,

When I run this code, I get the following error message
Msg 241, Level 16, State 1, Line 6
Syntax error converting datetime from character string.

DECLARE @Contracted_to datetime,
@depart datetime

SET @Contracted_to = CONVERT(char(8),'16:30:00',108)
SET @depart = CONVERT(char(8), '2008-05-30 12:08:02', 108)

SELECT
CASE WHEN @Contracted_to <= @depart
THEN CONVERT(char(8),DATEADD(ss, DATEDIFF(ss,@Contracted_to, @depart),
0),108)
ELSE '-' + CONVERT(char(8),DATEADD(ss, DATEDIFF(ss,@depart,
@Contracted_to), 0),108)
END

Once again, thanks for your assistance.

Regards
Dan

Post by John Bell

Hi Dan
12:08:02.013'
SELECT
0),108)
@datetime1),
0),108)
END
12:08:02.013'
SELECT
0),108)
@datetime1),
0),108)
END
John- Hide quoted text -
- Show quoted text -

Hi Dan

You have a casting problem when if you run (I have changed the date to a
safe format!)

DECLARE @Contracted_to datetime, @depart datetime

SET @Contracted_to = CONVERT(char(8),'16:30:00',108)
SET @depart = CONVERT(char(8), '20080530 12:08:02', 108)

SELECT @Contracted_to, @depart, CONVERT(char(8), '20080530 12:08:02', 108)

You get

----------------------- ----------------------- --------
1900-01-01 16:30:00.000 2008-05-30 00:00:00.000 20080530

If you changed that to:

DECLARE @Contracted_to datetime, @depart datetime

SET @Contracted_to = CONVERT(char(8),'16:30:00',108)
SET @depart = CONVERT(char(8), CAST('20080530 12:08:02' AS DATETIME), 108)

SELECT @Contracted_to, @depart, CONVERT(char(8), '20080530 12:08:02', 108)

You get

----------------------- ----------------------- --------
1900-01-01 16:30:00.000 1900-01-01 12:08:02.000 20080530

This all assumes that the two times are related to the same day!

John