Post reply

Datediff results

  • August 14, 2009 at 1:38 pm

    #135334

    When I execute this...

    select DATEDIFF("n",'08/14/2009 11:15',GETDATE())

    the results is 195

    when I execute this...

    select DATEDIFF("n",'08/14/2009 11:15',GETDATE())/60

    the result is 3, but 195/60 = 3.25

    Why is this?

    TIA

    Jim

  • August 14, 2009 at 1:44 pm

    #1039673

    You're dividing an integer by an integer so it's returning an integer. Try this instead -

    SELECT DATEDIFF(N,'08/14/2009 11:15',GETDATE())/60.00

  • August 14, 2009 at 1:48 pm

    #1039677

    Thanks!

    now how do i format it to be 3.40 not 3.4333

  • August 14, 2009 at 1:52 pm

    #1039678

    CONVERT should do it -

    SELECT CONVERT(decimal(18,2),DATEDIFF(N,'08/14/2009 11:15',GETDATE())/60.00)

  • August 14, 2009 at 1:54 pm

    #1039682

    Thanks again!!

    Jim

  • August 14, 2009 at 2:04 pm

    #1039690

    Here is my proc

    DECLARE

    @HMY AS INT,

    @STARTDATE AS DATETIME

    SELECT

    @HMY = HMY,

    @STARTDATE = DTSTART

    FROM

    MM2WODET

    WHERE

    DTFINISH IS NULL AND

    DTSTART IS NOT NULL AND

    HPERSON = @USERHMY

    IF @HMY > 0

    UPDATE MM2WODET SET

    DTFINISH = GETDATE(),

    DQUAN =CONVERT(DECIMAL(18,2),DATEDIFF("n",@STARTDATE,GETDATE())/60.00),

    SDESC = @status

    WHERE

    HMY = @HMY

    now i am getting invalid parameter 1 specified for datediff when i try and save the proc. DQUAN is defined as datetime

    any thoughts?

    Jim

  • August 14, 2009 at 2:09 pm

    #1039692

    jim.rasmussen (8/14/2009)

    Here is my proc

    DECLARE

    @HMY AS INT,

    @STARTDATE AS DATETIME

    SELECT

    @HMY = HMY,

    @STARTDATE = DTSTART

    FROM

    MM2WODET

    WHERE

    DTFINISH IS NULL AND

    DTSTART IS NOT NULL AND

    HPERSON = @USERHMY

    IF @HMY > 0

    UPDATE MM2WODET SET

    DTFINISH = GETDATE(),

    DQUAN =CONVERT(DECIMAL(18,2),DATEDIFF("n",@STARTDATE,GETDATE())/60.00),

    SDESC = @status

    WHERE

    HMY = @HMY

    now i am getting invalid parameter 1 specified for datediff when i try and save the proc. DQUAN is defined as datetime

    any thoughts?

    Jim

    Change this:

    DQUAN = CONVERT(DECIMAL(18,2),DATEDIFF("n",@STARTDATE,GETDATE())/60.00),

    to this

    DQUAN = CONVERT(DECIMAL(18,2),DATEDIFF(n,@STARTDATE,GETDATE())/60.00),

    Edit: You may still get an error as I'm not sure what an implicit conversion from decimal to datetime will do.

  • August 14, 2009 at 2:58 pm

    #1039721

    Thanks!!

    Jim

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply