three date duration

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

Answer 1

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

Answer 2

User-807418713 posted

Hello

How would be this code in sql 2008 format not support 

Answer 3

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.