none
Convert Datepart into minutes and seconds RRS feed

  • Question

  • How do I the below datediff (in bold) into minutes and seconds and not just seconds. 

    The return I get show in seconds

    If (@AgencyType = 'Law')
    Begin
    Select Jurisdiction,
    CallTaking_Performed_By,
    Percentile_Disc(.9)Within Group(Order By(DateDiff(s, Time_PhonePickUp, Time_CallEnteredQueue)))
    Over (Partition By CallTaking_Performed_By + Jurisdiction) As Received_to_Queue,
    DateDiff(n, '0:00:00',(Percentile_Disc(.9)Within Group(Order By Elapsed_InQueue_2_FirstAssign)
    Over (Partition By CallTaking_Performed_By + Jurisdiction))) As Queue_to_FirstAssigned
    From [Reporting_System].[dbo].[Response_Master_Incident] with (nolock)
    Where Jurisdiction in (@SearchJurisdiction) 
    and CallTaking_Performed_By in (@CallTaker) 
    and Agency_Type = 'LAW' 
    and Priority_Description in (@SearchPriority) 
    and Master_Incident_Number is not null 
    and problem not like '' 
    and problem not like '%hangup%' 
    and Elapsed_CallRcvd2InQueue > '00:00:00' 
    and Elapsed_InQueue_2_FirstAssign > '00:00:00' 
    and Response_Date between @StartDate and DateAdd(d,1,@EndDate)
    End

    Tuesday, October 15, 2019 5:01 PM

All replies

  • It is in Order by clause so it should not be any different with seconds or minutes.

    Order By(DateDiff(s, Time_PhonePickUp, Time_CallEnteredQueue)))

    Tuesday, October 15, 2019 5:04 PM
    Moderator
  • Hi DoaElite,

    Use 'minute' instead of 's' ,if you want to take minutes instead of seconds like below example-

    select 
    DateDiff(minute, '2019-10-16 00:21:00.260', '2019-10-16 00:23:00.260') as [minutes]
    ,DateDiff(s, '2019-10-16 00:21:00.260', '2019-10-16 00:23:00.260') as [seconds];
    
    /*
    minutes     seconds
    ----------- -----------
    2           120
    */
    
    

    In your query,

    As Jingyang Li states, even if you tried with minutes or seconds in Order by clause, it wont make any difference.

    if you want to try - use below,

    --For seconds
    Order By(DateDiff(s, Time_PhonePickUp, Time_CallEnteredQueue))
    --For minutes
    Order By(DateDiff(minute, Time_PhonePickUp, Time_CallEnteredQueue))

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks in Advance,
    Arulmouzhi

    Tuesday, October 15, 2019 6:58 PM
  • Maybe you mean that the output columns (seconds) should be represented in convenient form as minutes and seconds? If you want to do such formatting in SQL, then try something like this:

    select . . .

      format( dateadd( s, percentile_disc(.9) within group( order by DateDiff(s, Time_PhonePickUp, Time_CallEnteredQueue)), '00:00'), 'mm\:ss'  ) as  Received_to_Queue,

      . . .

     

    It assumes that the interval is less than an hour.



    • Edited by Viorel_MVP Tuesday, October 15, 2019 7:40 PM
    Tuesday, October 15, 2019 7:37 PM
  • Hi DoaElite,

     

    Thank you for your issue.

     

    I agree with Jingyang Li's advice . It is in Order by clause so it should not be any different with seconds or minutes.

     

    Or would you like to show '* minute: *  second'? Please try following script .

     
    DECLARE @Time_PhonePickUp DATETIME;
    DECLARE @Time_CallEnteredQueue DATETIME;
    DECLARE @timetaken VARCHAR(30);
    
    SET @Time_PhonePickUp = '2019-05-16 00:00:02';
    SET @Time_CallEnteredQueue = '2019-05-16 01:00:02';
    SELECT  @timetaken = cast(DATEDIFF(mi,@Time_PhonePickUp,@Time_CallEnteredQueue)as varchar(10))+' minute:'
    +cast((DATEDIFF(ss,@Time_PhonePickUp,@Time_CallEnteredQueue)-DATEDIFF(mi,@Time_PhonePickUp,@Time_CallEnteredQueue)*60)as varchar(10))+' second'
    
    PRINT @timetaken 
    /*
    60 minute:0 second
    */
    

     

    Best Regards,

    Rachel


    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.

    Wednesday, October 16, 2019 7:17 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    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.

    Monday, October 21, 2019 6:56 AM