CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlssms
user3311323
user3311323

Reputation:

select month and year from Date of birth IN sql

i have table with DOB column ('2012-05-29 00:00:00.000') and few other fields , i need to select the data for DOB between 6 months to 6 Years. I tried using the below SQL but this is not giving me the right data. any help will be appreciated. 

select * from dbo.xyz
where ( FLOOR(DATEDIFF(MONTH, birth_date , GETDATE()) % 12) >=6
      AND  FLOOR(DATEDIFF(DAY, birth_date , GETDATE()) / 365.25) <= 6
      )

Upvotes: 0

Views: 2771

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

When using dates, the advice is to use functions only on the non-column values. In other words, modify getdate(), not birth_date:

select *
from dbo.xyz
where birth_date between dateadd(year, -6,  getdate()) and dateadd(month, -6, getdate())

This has two advantages. First, it makes the where clause "sargable", which means an index can be used on the comparison. More importantly, the alternative of using datediff() doesn't quite work as expected. datediff() counts the number of calendar boundaries between two values. So, 2014-12-31 and 2015-01-01 are one day apart, one month apart, and even one year apart.

Upvotes: 3

Sean Lange
Sean Lange

Reputation: 33581

Here is another option that will allow indexing on birthdate.

select *
from dbo.xyz
where birthdate > DATEADD(YEAR, -6, GETDATE())
    and birthdate < DATEADD(MONTH, -6, GETDATE())

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93734

Try this

select * from dbo.xyz
where DATEDIFF(MONTH, birth_date , GETDATE()) between 6 and 72

Upvotes: 2

Related Questions