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

Debugging database errors with SQL Server Express

I am trying to debug a classic ASP application using Visual Web Developer Express and SQL Server Express editions. When my application tries to execute a stored procedure, I get the following error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available.

Is there a way for me to trace queries that are getting executed in SQL Server Express so I can see which stored procedure is failing? I seem to remember being able to do this with SQL Server 2000. Any other ideas on how to debug SQL Server errors like these using SQL Server Express?

TIA for the help.

Michael

Hi Michael,

There are a number of KB articles that mention this error, you should go to http://support.microsoft.com and search on your error to see if any of the existing articles address the issue. I found the following, fairly generic problem that produces this error, but without more informaiton it's just a shot in the dark:

269495 PRB: "Multiple-step OLE DB operation generated errors" when opening ADO connection
http://support.microsoft.com/default.aspx?scid=kb;EN-US;269495

You may also find more pople who can help with this type of problem in the ASP.net forums. You can find all the forums at http://forums.asp.net/ and the forum that is specifically about SQL Express in VWD at http://forums.asp.net/54/ShowForum.aspx.

For the record, SQL Profiler is not included in SQL Express. This was probably the tool you were using in SQL 2000.

Regards,

Mike Wachal
SQL Express team

-
Please mark your thread as Answered when you get your solution.