Search Date-Month togetherly in a query; ignoring year.

Question

Post reply

Search Date-Month togetherly in a query; ignoring year.

mail2payan

SSCrazy

Points: 2286
More actions
October 7, 2010 at 6:16 am

#103998

Hello Developers,
I am having a query.
I have 2 tables named as astrology & store_dob:
Table: astrology
ID (numeric); Zodiac (char, 10); Date_From (datetime); Date_to (datetime)
-------------------------------------
ID | Zodiac | Date_From | Date_to
-------------------------------------
1 | Aries | 21/03/1900 | 20/04/1900
2 | Taurus | 21/04/1900 | 21/05/1900
3 | Gemini | 22/05/1900 | 21/06/1900
-------------------------------------
Table: store_dob
ID (numeric); Name (varchar, 50); DOB (datetime)
-----------------------
ID | Name | DOB
-----------------------
1 | John | 26/03/1985
1 | Liz | 01/05/1970
-----------------------
You will see, I had kept year '1900' in all the Date_From & Date_to column as I am not concern of the year.
Now If someone want to search some particular date, say 26/03/1985; then the query should only search between date & month between Date_From & Date_to, ignoring year:
select * from astrology atr, store_dob sd
where sd.dob = '26/03/1985' between atr.Date_From and atr.Date_to
Result should give:
-------------------------------------
ID | Zodiac | Date_From | Date_to
-------------------------------------
1 | Aries | 21/03/1900 | 20/04/1900
-------------------------------------
Hope I can make you understand my problem.
Please help me!
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

I think this should do:

DECLARE @searchDate datetime

SET @searchDate = '1985-03-26'

SET @searchDate = DATEADD(day,DAY(@searchDate)-1,DATEADD(month, MONTH(@searchDate)-1, 0))

SELECT *

FROM astrology atr,

store_dob sd

WHERE @searchDate BETWEEN atr.Date_From AND atr.Date_to

Hope this helps

Gianluca

-- Gianluca Sartori

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 2

Here's an alternative expression:

SET @searchDate = DATEADD(year, DATEDIFF(year, @searchDate, 0), @searchDate)

Note that the two expressions give different answers if the @searchDate is a 29th February. Mine gives '1900-02-28', Gianluca's gives '1900-03-01'. This date shift occurs because 1900 wasn't a leap year. I've no idea if this is a problem from an astrological point of view!

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Which way Capricorn is recorded in your table?

_____________
Code for TallyGenerator

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Sergiy (10/10/2010)
Which way Capricorn is recorded in your table?

Interesting question. I'm curious.

-- Gianluca Sartori