SQL Datetime difference in days

Question

I am trying to find how many days are between two dates. I have been attempting to use this:

SET Days_Outstanding = DATEDIFF(day, CONVERT(datetime, LS_CHG_DTE_EQP, 1), CONVERT(datetime, Report_Date, 1))

However, this is not returning the number in days. It's returning a datetime in years that have nothing to do with my data set.

I'm not sure where the problem is, still fairly new to SQL. Also, I have Days_Outstanding defined as an int, could that be the problem? Thanks!

EDIT

Thanks everyone! Not sure what happened, I changed a few things to what you suggested and it wound up working when I went back to what I had originally. Not sure what happened. Thanks thought!

Answer 1

Have you tried just

SET Days_Outstanding = CONVERT(datetime, LS_CHG_DTE_EQP, 1) - CONVERT(datetime, Report_Date, 1))

Answer 2

DATEDIFF returns integer

However, the number of days could be plain wrong depending on the values/datatype of LS_CHG_DTE_EQP/Report_Date and locale

Also, DATEDIFF works on day boundaries not 24 hour periods. So 23:56 to 00:02 is one day.

Otherwise, please add example input and output.

Answer 3

Example

select DATEDIFF(dd,'20100101',getdate())

will return 227 today

so in your case assuming LS_CHG_DTE_EQP and Report_Date are both datetime columns

SET @Days_Outstanding = DATEDIFF(day, LS_CHG_DTE_EQP, Report_Date)