Count days in date range?

Question

I have a query like this:

SELECT COUNT(*) AS amount
FROM daily_individual_tracking
WHERE sales = 'YES'
AND daily_individual_tracking_date BETWEEN '2010-01-01' AND '2010-03-31'

I am selected from a date range. Is there a way to also get the total days in the date range?

Answer 1

This depends on what SQL server you're using.

If you're using MS-SQL Server, you can use the function DateDiff

Answer 2

Not really clear if you are looking for

DATEDIFF('2010-03-31', '2010-01-01')

or

COUNT(DISTINCT daily_individual_racking_date)

Answer 3

What exactly are you trying to count? The total number of distinct values of daily_individual_tracking_date? Do you need it in the same query as the count(*) query?

Answer 4

You can use the MySQL datediff function:

SELECT DATEDIFF('2010-01-01','2010-01-31') AS DiffDays

It should return a floating point, where 1.0 represents a single day.

And for MS SQL use ,

SELECT DATEDIFF( day ,'2010-01-01','2010-01-31') AS DiffDays

Answer 5

I'm not sure which SQL you are using. TSQL has a DATEDIFF that will count the number of days between two dates. See this