Friday, February 25, 2011

sql date percentage

To get pecentage of a date in sql query you just have to think in different way.
Dates belong to a year. right!
Year has 365 days. right!
year percentage can be easily calculated in term of days right!
and added or subtracted from date to get the percent of date

here is example

Number of days from percentage
365*Percentage/100

If I want to add 20 pecent in a date

DATE_ADD(YourDate ,INTERVAL ROUND(365*20/100) DAY )

if i want to subtract 20 percent date

DATE_SUB(YourDate ,INTERVAL ROUND(365*20/100) DAY )

A more detailed example
I want a room from 2nd jan 2015 to 7th jan 2015
now search engine should find result with 10% +- dates
here is query which will fullfill the above example

select * from room where
room.fromdate between DATE_SUB(room.fromdate ,INTERVAL ROUND(365*10/100) DAY ) and DATE_ADD(room.fromdate ,INTERVAL ROUND(365*10/100) DAY )

No comments:

Post a Comment