locked
three date duration RRS feed

  • Question

  • User-807418713 posted

    Hello
    lets us assume i have 3 column as datetime fields and today date is 27-Dec-2020 my table data is like this

    ITEM NAME Original Delivery Date Date1  Date2 Date3
    ITEM1 20-Dec-2020 21-Dec-2020 24-Dec-2020 27-Dec-2020
             

    I want resultset like this below

    ITEM NAME Original Delivery Date (ODD) Date1  Date2 Date3 DELIVERY DETAILS TOTAL TIME
    ITEM1 20-Dec-2020 21-Dec-2020 24-Dec-2020 27-Dec-2020 20-DEC-2020 = 1 day delay from (ODD), 24-Dec-2020 = 4 days delay from (ODD), 27-Dec-2020 = 7 days delay from (ODD) 7 Days

    Thank you

    Sunday, December 27, 2020 5:42 PM

Answers

  • User753101303 posted

    Then start maybe with:

    SELECT CAST(DATEDIFF(day,'20210101','20210110') AS VARCHAR)+' day'+
    CASE WHEN DATEDIFF(day,'20210101','20210110')>1 THEN 's' ELSE '' END
    
    SELECT CAST(DATEDIFF(day,'20210101','20210102') AS VARCHAR)+' day'+
    CASE WHEN DATEDIFF(day,'20210101','20210102')>1 THEN 's' ELSE '' END

    What you looked for is DATEDIFF to get the difference between two date/time using the unit you want. Then CAST(x as VARCHAR) allows to convert this intzeger to a string so that you can add another string.

    Generally speaking you can use for example Date and Time Data Types and Functions - SQL Server (Transact-SQL) | Microsoft Docs to see available date/time functions.

    CASE condition THEN  expression ELSE expression END allows to return a value based on conditions.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 29, 2020 9:39 AM

All replies

  • User753101303 posted

    Hi,

    Assuming SQL Server it seems you are looking for https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15

    For example (depending on your SQL Server version):

    WITH a AS
    (
    	SELECT CAST('20-Dec-2020' AS DATE) AS ODD,
    CAST('21-Dec-2020' AS DATE) AS Date1,
    CAST('24-Dec-2020' AS DATE) AS Date2,
    CAST ('27-Dec-2020' AS DATE) AS Date3 ), b AS ( SELECT *,
    DATEDIFF(day,ODD,Date1) AS Delay1,
    DATEDIFF(day,ODD,Date2) AS Delay2,
    DATEDIFF(day,ODD,Date3) AS Delay3
    FROM a ) SELECT FORMAT(delay1,'n0')+' day'+IIF(delay1>1,'s','') +', '+FORMAT(delay2,'n0')+' day'+IIF(delay2>1,'s','') +', '+FORMAT(delay3,'n0')+' day'+IIF(delay3>1,'s','') FROM b -- shows 1 day, 4 days, 7 days

    Sunday, December 27, 2020 6:59 PM
  • User-807418713 posted

    Hello

    How would be this code in sql 2008 format not support 

    Tuesday, December 29, 2020 8:02 AM
  • User753101303 posted

    Then start maybe with:

    SELECT CAST(DATEDIFF(day,'20210101','20210110') AS VARCHAR)+' day'+
    CASE WHEN DATEDIFF(day,'20210101','20210110')>1 THEN 's' ELSE '' END
    
    SELECT CAST(DATEDIFF(day,'20210101','20210102') AS VARCHAR)+' day'+
    CASE WHEN DATEDIFF(day,'20210101','20210102')>1 THEN 's' ELSE '' END

    What you looked for is DATEDIFF to get the difference between two date/time using the unit you want. Then CAST(x as VARCHAR) allows to convert this intzeger to a string so that you can add another string.

    Generally speaking you can use for example Date and Time Data Types and Functions - SQL Server (Transact-SQL) | Microsoft Docs to see available date/time functions.

    CASE condition THEN  expression ELSE expression END allows to return a value based on conditions.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, December 29, 2020 9:39 AM