locked
calculate minuites from two time fields RRS feed

  • Question

  • Hi ,

    i have 2 fields start_time and End_time ,from these 2 fields i wanted to calculate difference of those fields in minutes.

    this one i am able to achieve with below query.

    select datediff(MI,CAST(START_TIME AS time),CAST(END_TIME AS time)) as Time_MI from sample;

    the typical part here is in my database some rows has nulls for start_time and end_time,so in place of nulls if it is start time i need to replace with '08:00' and if it is end_time replace with '15:00'.

    this one also i am able to achieve with below queries.

    isnull(START_TIME,'08:00') as START_TIME

    isnull(END_TIME,'15:00') as END_TIME

    after this replacing nulls with above values its not calculating difference for those values and it is calculating only the values existed before.

    the final query is this,could anyone help me where is the mistake. 

    select isnull(START_TIME,'08:00') as START_TIME,isnull(END_TIME,'15:00') as END_TIME,

    datediff(MI,CAST(START_TIME AS time),CAST(END_TIME AS time)) as Time_MI from sample;

    thanks,


    Thursday, August 16, 2018 10:24 AM

Answers

  • Hi,

    You need also put ISNULL() function in DATEDIFF(). They are in the same level in SELECT statement.

    Please try following query:

    create table #test
    (   
        ID int,
        Start_Time time,
        End_Time time
    )
    
    insert into #test VALUES
    (1,null,'09:35'),
    (2,'06:00','10:35'),
    (3,null,null),
    (4,'09:40',null)
    
    select ID,isnull(START_TIME,'08:00') as START_TIME,isnull(END_TIME,'15:00') as END_TIME,
    datediff(MI,CAST(isnull(START_TIME,'08:00') AS time),CAST(isnull(END_TIME,'15:00') AS time)) as Time_MI
    from #test
    
    drop table #test

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Xi Jin Monday, August 20, 2018 1:25 AM
    • Marked as answer by ds_999 Saturday, August 25, 2018 7:53 PM
    Friday, August 17, 2018 1:40 AM

All replies

  • hello

    any one could me assist here.

    Thursday, August 16, 2018 1:26 PM
  • I think what you're looking at is this?

    select datediff(MI,CAST(isnull(START_TIME,'08:00') AS time),CAST(isnull(END_TIME,'15:00') AS time)) as Time_MI

    from sample;



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Xi Jin Monday, August 20, 2018 1:25 AM
    Thursday, August 16, 2018 1:33 PM
  • Hi,

    You need also put ISNULL() function in DATEDIFF(). They are in the same level in SELECT statement.

    Please try following query:

    create table #test
    (   
        ID int,
        Start_Time time,
        End_Time time
    )
    
    insert into #test VALUES
    (1,null,'09:35'),
    (2,'06:00','10:35'),
    (3,null,null),
    (4,'09:40',null)
    
    select ID,isnull(START_TIME,'08:00') as START_TIME,isnull(END_TIME,'15:00') as END_TIME,
    datediff(MI,CAST(isnull(START_TIME,'08:00') AS time),CAST(isnull(END_TIME,'15:00') AS time)) as Time_MI
    from #test
    
    drop table #test

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Xi Jin Monday, August 20, 2018 1:25 AM
    • Marked as answer by ds_999 Saturday, August 25, 2018 7:53 PM
    Friday, August 17, 2018 1:40 AM