decimal to datetime puzzler

Ok, here's a puzzler for you...

It's the classic story; I have inherited a system etc etc which stores timesheet information.

The design is, as you'd expect, "intriguing".

There are fields to store the number of hours worked as a decimal; Rate1 & Rate2
There are also fields used throughout the application which are the datetime equivalents (from the base date) of these times; Eng_Hours_Rate1 & Eng_Hours_Rate2.

The problem is, the Eng_Hours_Rate fields do not get updated when a user changes the Rate value(s).

Now because I don't know the language the FE is programmed in too well, I thought I'd stick to something I am a bit better in.

So I thought - UPDATE TRIGGER!

However, I can't get my head around the conversion calculation - below shows the closest I have got yet. When I set the Rate field to 2.5, I get the datetime value 1900-01-01 02:29:59.940 which is very close, but not quite!

Any ideas on what to do to get this working are greatly appreciated :D

Here's the ddl of the relevant fields

--Create mock table
CREATE TABLE timesheet_mod (
UniqueID int identity(1,1) primary key NOT NULL
, Eng_Hours_Rate1 datetime NULL
, Eng_Hours_Rate2 datetime NULL
, Rate1 decimal(5,2) NULL
, Rate2 decimal(5,2) NULL
)

Here's some test data

--Insert test data
SET NOCOUNT ON
INSERT INTO timesheet_mod (Eng_Hours_Rate1, Eng_Hours_Rate2, Rate1, Rate2)
SELECT '1900-01-01 01:30:00.000','1900-01-01 01:00:00.000',1.50 ,1.00 UNION ALL
SELECT '1900-01-01 03:00:00.000','1900-01-01 00:00:00.000',3.00 ,0.00 UNION ALL
SELECT '1900-01-01 04:30:00.000','1900-01-01 00:30:00.000',4.50 ,0.50
SET NOCOUNT OFF
GO

Here's my attempt at the trigger

CREATE TRIGGER rate_Update
ON timesheet_mod
FOR UPDATE
AS
UPDATE timesheet_mod
SET eng_hours_rate1 = (u.rate1 / 24)
, eng_hours_rate2 = (u.rate2 / 24)
FROM timesheet_mod t
INNER
JOIN inserted u
ON u.uniqueid = t.uniqueid
WHERE t.UniqueID IN (SELECT UniqueID FROM inserted)
GO

And here's the test bit followed by a cleanup

SELECT * FROM timesheet_mod WHERE uniqueid IN (1,2,3)

UPDATE timesheet_mod
SET rate1 = 2.5
WHERE uniqueid IN (1,2)

SELECT * FROM timesheet_mod WHERE uniqueid IN (1,2,3)

--Clean up
DROP TRIGGER rate1_Update
DROP TABLE timesheet_mod

If I've not made this clear, or you fancy insulting my methods vigorously - please feel free ;)

EDIT: In theory the users should only ever be entering timesheet entries accurate to the nearest 1/4 of an hour (or so I'm told, ha!)Depends on the minimum unit you use perhaps.

DECLARE @.date AS DATETIME
, @.dec AS DECIMAL(5, 2)

SELECT @.date = 0
, @.dec = 2.5

SELECT @.date
, @.dec

SELECT @.dec = @.dec * 60
, @.date = DATEADD(mi, @.dec, @.date)

SELECT @.date|||Solved it

CREATE TRIGGER rate_Update
ON timesheet_mod
FOR UPDATE
AS
UPDATE timesheet_mod
SET eng_hours_rate1 = DateAdd(mi, u.rate1 * 60, 0)
, eng_hours_rate2 = DateAdd(mi, u.rate2 * 60, 0)
FROM timesheet_mod t
INNER
JOIN inserted u
ON u.uniqueid = t.uniqueid
WHERE t.UniqueID IN (SELECT UniqueID FROM inserted)
GO|||Solved itBlooming cheek :p|||Ha! I didn't realise you had posted that Poots - wrapped up in my own little bubble over here today :D

According to my business rules this should be AOK - but can obviously modified to be more accurate by using

DateAdd(ss, u.rate1 * 3600, 0)

Etc.

Appreciate the help Poots :)|||No probs. I suppose it is unlikely that anyone would bother entering a decimal representation of an hour that goes down to seconds but you never know...|||I've been assured that the FE won't allow it - but we all know what that really means.

Nice to have you back Poots :)