Date diff with negative time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dan Bridgland - 30 May 2008 10:52 GMT

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

Reply to this Message

John Bell - 30 May 2008 12:15 GMT

> Hi,
>
[quoted text clipped - 45 lines]
> 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

Reply to this Message

Dan Bridgland - 30 May 2008 13:39 GMT

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

> > Hi,
>
[quoted text clipped - 76 lines]
>
> - Show quoted text -

Reply to this Message

John Bell - 30 May 2008 14:53 GMT

> "Dan Bridgland" <DanBridgl...@gmail.com> wrote in message
>
[quoted text clipped - 82 lines]
>
> - 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

Reply to this Message