question

Denzil avatar image
Denzil asked

Date occurrence

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

querydatetime
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

· Write an Answer
Squirrel 1 avatar image
Squirrel 1 answered 
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
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso avatar image
Peso answered

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)
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.