I want to find out how many times did 1st Jan occur between two dates - for example from 01-jan-2005 to 01-jan-2010
use F_TABLE_DATE here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
select count(*)
from F_TABLE_DATE ( '2005-01-01', '2010-01-01' )
where MONTH = 1
and DAY_OF_MONTH = 1
Or keep it simple?
DECLARE @FromDate DATETIME,
@ToDate DATETIME
SELECT @FromDate = '01-jan-2005',
@ToDate = '01-jan-2010'
SELECT DATEDIFF(YEAR, DATEADD(DAY, -1, @FromDate), @ToDate)
No one has followed this question yet.