2

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!

Community
  • 1
  • 1
user416516
  • 137
  • 2
  • 9

3 Answers3

4

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.

Khaled.K
  • 5,828
  • 1
  • 33
  • 51
gbn
  • 422,506
  • 82
  • 585
  • 676
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)
SQLMenace
  • 132,095
  • 25
  • 206
  • 225
0

Have you tried just

SET Days_Outstanding = CONVERT(datetime, LS_CHG_DTE_EQP, 1) - CONVERT(datetime, Report_Date, 1))
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155