Subtracting date using DATEDIFF function and getting NULL as results

datediff mysql
sql datediff between two dates
calculate days between two dates in sql query
datediff snowflake
datediff redshift
datediff sql
dateadd
get months between two dates in sql server

So I'm tasked with finding the delay in weeks for each order. I've used the DATEDIFF function and I'd like to believe I'm on the right track but when I use it I get NULL as the result. The data type for each column are both date.

SELECT DISTINCT Sales.Orders.custid, Sales.Customers.companyname,
CASE
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 7 AND  DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 14 THEN '1 Week'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 14 AND DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 21 THEN '2 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 21 AND DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 28 THEN '3 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 28 AND DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 35 THEN '4 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 35 AND DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 42 THEN '5 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) = 42 AND DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 49 THEN '6 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) > 49 THEN '7+ Weeks'
    ELSE 'Unknown'
END AS Order_Delay
FROM Sales.Orders, Sales.Customers
ORDER BY 
Order_Delay ASC;

I'm using MS SQL Server Management Studio 2016.

Try rewriting your query like this:

SELECT DISTINCT Sales.Orders.custid, Sales.Customers.companyname,
CASE
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) >= 7 AND  DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 14 THEN '1 Week'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 21 THEN '2 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 28 THEN '3 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 35 THEN '4 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 42 THEN '5 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 49 THEN '6 Weeks'
    WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) >= 49 THEN '7+ Weeks'
    ELSE 'Unknown'
END AS Order_Delay
FROM Sales.Orders, Sales.Customers
ORDER BY 
Order_Delay ASC;

I think you want to check whether difference is in particular range (from 7 to 14, etc.).

So I corrected first cndition:

WHEN DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) >= 7 AND  DATEDIFF(DAY, Sales.Orders.shippeddate, Sales.Orders.orderdate) < 14 THEN '1 Week'

You could not use BETWEEN here, as its range includes also broders of a set.

For other cases, you don't need to check whether the difference is greater than, eg. in second WHEN you know that the difference is >=14, since it failed first condition, etc.

SQL Server DATEDIFF Function By Practical Examples, In this tutorial, you will learn how to use SQL Server DATEDIFF() function to calculate date_part is the part of date e.g., a year, a quarter, a month, a week that you want The DATEDIFF() function returns an error if the result is out of range for shipment FROM sales.orders WHERE shipped_date IS NOT NULL ORDER BY  If only a date value is assigned to a variable of a time or date data type, DATEDIFF sets the value of the missing time part to the default value: 00:00:00. If either startdate or enddate have only a time part and the other only a date part, DATEDIFF sets the missing time and date parts to the default values.

FROM Sales.Orders, Sales.Customers is an old-fashioned way of forming a cross join. This is probably accidental but the impact of this can be awful both in terms of performance but also the results can be plain wrong - and they are in your example. It is for this reason that I always recommend you use explicit join syntax such as inner join and cease using commas as a way to define the from clause.

You simply have to properly join the 2 tables, otherwise every order is applied to every customer and the results would be quite wrong. I have guessed that join, but it should look something like the one seen below:

SELECT /* DISTINCT ?? */
    Sales.Orders.custid
  , Sales.Customers.companyname
  , CASE
        WHEN ca.Order_Delay >= 7 THEN '7+ Weeks'
        WHEN ca.Order_Delay >= 1 AND ca.Order_Delay < 7 THEN CAST(ca.Order_Delay AS varchar) + ' Weeks'
        ELSE 'Unknown'
    END AS order_delay
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c ON o.custid = c.id
CROSS APPLY (
    SELECT
        FLOOR(DATEDIFF(DAY, o.shippeddate, o.orderdate) / 7)
    ) ca (order_delay)
ORDER BY
    order_delay ASC
;

In SQL Server is possible to use cross apply as a way to perform a calculation, and give that calculation an alias that you can then use in the select clause. This can have the effect of making your code somewhat easier to read, but this is optional.

Above I have suggested a way to use floor() which you should read about here: https://docs.microsoft.com/en-us/sql/t-sql/functions/floor-transact-sql?view=sql-server-2017

nb: If you want to show data for unshipped orders then you may need to change to an outer apply, and if an order is unshipped the the datediff() function would return NULL and your case expression would need to explicitly cater for NULLs

SELECT /* DISTINCT ?? */
    Sales.Orders.custid
  , Sales.Customers.companyname
  , CASE
        WHEN ca.Order_Delay >= 7 THEN '7+ Weeks'
        WHEN ca.Order_Delay >= 1 AND ca.Order_Delay < 7 THEN CAST(ca.Order_Delay AS varchar) + ' Weeks'
        WHEN ca.Order_Delay IS NULL then 'Unshipped'
        ELSE 'Unknown'
    END AS order_delay
FROM Sales.Orders AS o
INNER JOIN Sales.Customers AS c ON o.custid = c.id
OUTER APPLY (
    SELECT
        FLOOR(DATEDIFF(DAY, o.shippeddate, o.orderdate) / 7)
    ) ca (order_delay)
ORDER BY
    order_delay ASC
;

SQL Server DATEDIFF() Function, Table SQL Alter Table SQL Constraints SQL Not Null SQL Unique SQL Primary Key SQL Example. Return the difference between two date values, in years: SELECT The DATEDIFF() function returns the difference between two dates. Works in: SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data​  DATEDIFF¶ Calculates the difference between two date, time, or timestamp expressions based on the date or time part requested. The function returns the result of subtracting the second argument from the third argument. The minus sign (“-“) can also be used to subtract dates. See also: TIMEDIFF, TIMESTAMPDIFF

https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

DATEDIFF ( datepart , startdate , enddate )

My guess is that in your system shippeddate is mostly later or equal to orderdate, so instead of

FLOOR(DATEDIFF(DAY, o.shippeddate, o.orderdate) / 7)

you might want

FLOOR(DATEDIFF(DAY, o.orderdate, o.shippeddate) / 7)

to check this assumption you might want to add ca.Order_Delay (as per code suggested by @Used_by_already) to list of selected columns and see what values are there. My bet is they are all negative.

DATEDIFF (Transact-SQL), Transact-SQL reference for the DATEDIFF function. Returns the numerical difference between a start and end date based on datepart. The following table lists all the valid datepart values. DATEDIFF accepts These examples use different types of expressions as arguments for the startdate and enddate parameters. Hi everyone. I am new to Tableau and have a query with the DATEDIFF function. My start dates and end dates are coming from 2 different data sources, and the function I entered seems to work for some of the dates, and not for others. Bizarre! The formatting of all dates is the same so I can't work out why some respond to my formula and others do

You are looking for exactly 7, 14, 21 etc. You need >= 7 instead (and repeat for the rest...).

DateDiff function (Visual Basic for Applications), String expression that is the interval of time you use to calculate the difference between date1 and date2. date1, date2, Required; Variant (Date). SQL Server DATEDIFF() function examples. Let’s take some examples of using the DATEDIFF() function to understand it better. A) Using DATEDIFF() function to compare the differences between two date values. This example uses the DATEDIFF() function to compare the difference between two dates in various date parts:

DATEDIFF, The function returns the result of subtracting the second argument from the third argument. The minus sign (“-“) can also be used to subtract dates. See also:. If interval is Week ("ww"), however, the DateDiff function returns the number of calendar weeks between the two dates. It counts the number of Sundays between date1 and date2. DateDiff counts date2 if it falls on a Sunday; but it doesn't count date1, even if it does fall on a Sunday.

MySQL DATEDIFF Function, In this tutorial, you will learn how to use MySQL DATEDIFF function to calculate the number of days between two date values. The syntax of the MySQL DATEDIFF function is as follows: Foreign Key · UNIQUE Constraint · CHECK Constraint · NOT NULL · Sequence · ALTER TABLE · ADD COLUMN · DROP COLUMN  Every column has a start date, but not every column has a finish date. I am trying to create a column C that has the age of the record. Using DATEDIFF=(column A, column B, DAY) will return the age in days of the columns with an end date, but since there are null values for those still ongoing, it returns nothing for them. I still need that age!

SQL/400 Developer's Guide, Figure 5.8 Sample Retrieval Using Dates and Durations Days (ShipDate) - Cust ID which results in a dif. ference of 14 days between the Days function values for the two columns. For arithmetic expressions, if one of the operands is null, the result of the You can also add a duration to or subtract a duration from a date. Try testing to see if oDate or oShippedDate is a date or can be interpreted as a date and if so return null or zero or a negative number. I was going to suggest testing to see if one is null, but the DateDiff function handles nulls

Comments
  • If you're using MS SQL, why did you tag the question with MySQL?
  • =7 and <14 - is there any chance that 7 becomes >=14? Did you mean >=7 ? <49 and > 49 = lost 49
  • And, of course, why not floor(datediff(dd...) / 7)
  • and you might want to join the tables properly, you have just created a Cartesian product of those 2 tables, I would not trust the current results at all. Not surprising you added distinct to reduce the number os rows, but that doesn't fix the underlying problem.
  • I'm not too familiar with the floor function and how it would be used here?
  • I made the changes and now I'm just getting unknown as the result.
  • Suggest you swap the 2 dates inside the datediff function as suggested below by ,Ivan Starostin. If the datediff result is negative then the else condition is the one that works most of the time.
  • While you are correct about the edit that is required, that doesn't explain NULL values as there is an ELSE clause on the CASE expression.
  • @Nick Then the OP isn't showing us the whole picture. That CASE expressions can't ever return NULL.
  • @MatBailie indeed. But that never happens on SO! :)