Show Date-Month togetherly in a query.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Show Date-Month togetherly in a query.

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2010-10-07 : 06:39:08

Hello Developers,

I am having a query.
I have a table named as astrology:

-------------------------------------
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
-------------------------------------

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 and give me result:

-------------------------------------
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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-07 : 07:17:48

We need to know the datatypes of Date_From and Date_to in your table and also we need to know how the searchdate is given to the query.

No, you're never too old to Yak'n'Roll if you're too young to die.

daipayan
Posting Yak Master

181 Posts

Posted - 2010-10-07 : 07:48:56

Sir,

Datatypes for Date_From and Date_to is datetime.

Sorry, I forget to mention another table as store_dob:

-----------------------
ID | Name | DOB
-----------------------
1 | John | 26/03/1985
1 | Liz  | 01/05/1970
-----------------------

Now, I am giving searchdate query as:

select * from astrology atr,  store_dob sd 
where sd.dob between atr.Date_From and atr.Date_to

Daipayan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-10-07 : 09:46:36

[code]DECLARE @Sample TABLE
(
ID TINYINT,
Zodiac VARCHAR(12),
FromDate DATETIME,
ToDate DATETIME
)

INSERT @Sample
SELECT 1, 'Aries', '19000321', '19000420' UNION ALL
SELECT 2, 'Taurus', '19000421', '19000521' UNION ALL
SELECT 3, 'Gemini', '19000522', '19000621'

DECLARE @Person TABLE
(
ID TINYINT,
Name VARCHAR(20),
DoB DATETIME
)

INSERT @Person
SELECT 1, 'John', '19850326' UNION ALL
SELECT 1, 'Liz', '19700501'

SELECT p.ID,
p.Name,
p.DoB,
f.Zodiac
FROM @Person AS p
CROSS APPLY (
SELECT w.Zodiac
FROM @Sample AS w
WHERE p.DoB >= DATEADD(YEAR, DATEPART(YEAR, p.DoB) - 1900, w.FromDate)
AND p.DoB <= DATEADD(YEAR, DATEPART(YEAR, p.DoB) - 1900, w.ToDate)
) AS f[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources