asked on

DATEDIFF360

Hi Experts,

I want some help about this, I know that DATEDIFF (dd,start_date,end_date) returns me the number of days between those dates, but they are based on the 365(366) days of each year. Now I need the same function but based on 360 days, just like the excel function "DAYS360", is there any way to do it?

Thanks

JHL

DireOrbAnt

As far as I know, DATEDIFF is based on "actual days" not a static 365. It takes two dates and returns the actual # of days (in your example) between the two.

DireOrbAnt

For DAYS360 (financial count), I guess you could do:

DATEDIFF(MONTH, @start_date, @end_date) * 30 + DAY(@end_date) - DAY(@start_date)

Lowfatspread

Hi _JHL_,
don't forget to deal with the time component of the date columns/fields as well....

Cheers!

imran_fast

Hi ,

DATEDIFF (dd,start_date,end_date) will give you number of days between start date and end date

i think what you want is to have no of months between two dates and then multiply it by 30
DATEDIFF (m,start_date,end_date) * 30 + DATEDIFF (dd, cast (year(end_date) as varchar(4)) + cast (month(end_date) as varchar(2)) +'01',end_date)

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_JHL_

ASKER

Thanks very much ScottPletcher, you have solved my problem. Thanks again ;)

PTBNA

Watch out for Feb 28th and 29th. I see in your sample you did not take it into account.