How can I convert dates into numerical values so I can use them in calculations?

1

I am trying to transform a column (using an Expression) to reflect order lead times. I have data for Order_Date and Recv_Date, and I would like to be able to subtract them so I easily know how many days the order took to be received.

When I tried I received the following error: “The expression ([Order_Date]-[Recv_Date]) is not a valid transfortmation. Exception: The data types nvarchar and nvarchar are incompatible in the subtract operator.”

I also have other metrics I would like to capture that use data currently in date form data. Anyone know how I can easily convert the dates to be used in calculations?

Thanks!

1 Like
2

I would use the DATEDIFF command (SQL Server DATEDIFF() Function) . This would also require converting the date fields into a date data type using CAST. Try this:

DATEDIFF(day, CAST([Order_Date] as date), CAST([Recv_Date] as date))

  1. Cast both fields into a date format.
  2. Find the date difference (in days) between the two.