CODEWITHSUNDEEP
sqlsql-server-2008
PanosPlat
PanosPlat

Reputation: 958

SQL find period that contain dates of specific year

I have a table (lets call it AAA) containing 3 colums ID,DateFrom,DateTo

I want to write a query to return all the records that contain (even 1 day) within the period DateFrom-DateTo of a specific year (eg 2016).

I am using SQL Server 2005

Thank you

Upvotes: 1

Views: 1287

Answers (3)

Brahim LAMJAGUAR
Brahim LAMJAGUAR

Reputation: 1354

Well you can use the following query 

select * from Table1 
WHERE DateDiff(day,DateFrom,DateTo)>0 
      AND YEAR(DateFrom) = YEAR(DateTo)

And here is the result:

enter image description here

Enjoy :D !

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82534

Another way is this:

SELECT <columns list>
FROM AAA
WHERE DateFrom <= '2016-12-31' AND DateTo >= '2016-01-01'

If you have an index on DateFrom and DateTo, this query allows Sql-Server to use that index, unlike the query in Max xaM's answer.
On a small table you will probably see no difference but on a large one there can be a big performance hit using that query, since Sql-Server can't use an index if the column in the where clause is inside a function

Upvotes: 3

Max xaM
Max xaM

Reputation: 348

Try this:

SELECT * FROM AAA
WHERE DATEPART(YEAR,DateFrom)=2016 OR DATEPART(YEAR,DateTo)=2016

Upvotes: 1

Related Questions