Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

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

    Here's the ddl of the relevant fields
    Code:
    --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
    Code:
    --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
    Code:
    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
    Code:
    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!)
    Last edited by gvee; 11-08-07 at 11:08.
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depends on the minimum unit you use perhaps.

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

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Solved it
    Code:
    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
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Solved it
    Blooming cheek

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ha! I didn't realise you had posted that Poots - wrapped up in my own little bubble over here today

    According to my business rules this should be AOK - but can obviously modified to be more accurate by using
    Code:
    DateAdd(ss, u.rate1 * 3600, 0)
    Etc.

    Appreciate the help Poots
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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....

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •