CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Eric Parra
Eric Parra

Reputation: 11

Working with a Date Expression (MS SQL)

Good day everyone.

I'm learning how to write queries in SQL, specifically MS SQL 2012.

One of the examples from the book I'm using asks the following:

"How many days did it take to ship each order?"

The SQL query from my book is:

SELECT OrderNumber, CAST(ShipDate - OrderDate as INTEGER) AS DaysToShip FROM Orders

I type the above query in the query window and press execute but then I get the following error message:

Msg 8117, Level 16, State 1, Line 1
Operand data type is invalid for subtract operator.

FYI, the field OrderNumber is an Integer field type while ShipDate and OrderDate are Date field types.

Is this not the correct syntax to use for MS SQL 2012? I'm running this query in Microsoft SQL Server Management Studio. Can someone please point me in the right direction? Any help/hints would be greatly appreciated!

Upvotes: 1

Views: 85

Answers (1)

jpw
jpw

Reputation: 44911

MS SQL doesn't support calculations on dates in that way (some other databases do, for example Oracle and DB2). Use the datediff function instead:

SELECT OrderNumber, DATEDIFF(day, OrderDate, ShipDate) AS DaysToShip FROM Orders

The function is defined as DATEDIFF ( datepart , startdate , enddate ) and returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

Upvotes: 1

Related Questions