[Resolved] Find date period when year don't care. How ?

fabiuz

Hi all,

I have one date and I want to get the zodiac sign of it, so I need to use a date interval in the Where clause but I have to don't care about the year.

I still did't found any mysql function to do this purpose :-(

I think I need somethink like this:


SELECT CASE date_of_born
      WHEN period 1 THEN 'Aries'
      WHEN period 2 THEN 'Taurus'
      WHEN p3 THEN 'Gemini'
      WHEN p4 THEN 'Cancer'
      WHEN p5 THEN 'Leo'
      WHEN p6 THEN 'Virgo'
      WHEN p7 THEN 'Libra'
      WHEN p8 THEN 'Scorpio'
      WHEN p9 THEN 'Sagittarius'
      WHEN p10 THEN 'Capricorn'
      WHEN p11 THEN 'Acquarius'
      WHEN p12 THEN 'Pisces'
      ELSE ''
 END date_of_born
FROM mytable

what i am unableto to find is a way to check month and day only 🙁 , periods should be something like:

date_of_bird >= 05-21 AND date_of_bird < 04-19

Anybody knows how to so somethink like this ?

Thanks a lot

nemonoman

WHERE (date_format(dob),'%m%d') between '0521' AND '0620'

fabiuz

Unfortunatly not :-(

mysql doen not have beetwen and statement, so i have tryed with

SELECT idutente,dob FROM mytable
WHERE (date_format(dob),'%m%d') >= '0521'
AND (date_format(dob),'%m%d') < '0420'

but it doesn't work, I get a syntax error 🙁

Thank you

Stelf

'Between' works for me in MYSQL.

WHERE dateField BETWEEN '2004-01-01' AND '2004-02-01'

fabiuz

sorry, my mistake..

Anyway, I have tryed with:

WHERE (date_format(dob),'%m%d') BETWEEN '0521' AND '0620'

like suggested and I get a syntax error :-(

I think because of date compares to string... and I am not pretty shure that the year will be not considered when checking...

Anybody could hekp about it?

Thanks a lot

Sxooter

Ok, here's what I did (in postgresql, but it should port pretty easy)

CREATE TABLE signs (
    id serial NOT NULL,
    sign text,
    startdate date,
    enddate date
);

INSERT INTO signs VALUES (2, 'Taurus', '2000-04-20', '2000-05-20');
INSERT INTO signs VALUES (3, 'Gemini', '2000-05-21', '2000-06-20');
INSERT INTO signs VALUES (4, 'Cancer', '2000-06-21', '2000-07-22');
INSERT INTO signs VALUES (5, 'Leo', '2000-07-23', '2000-08-22');
INSERT INTO signs VALUES (6, 'Virgo', '2000-08-23', '2000-09-22');
INSERT INTO signs VALUES (7, 'Libra', '2000-09-23', '2000-10-22');
INSERT INTO signs VALUES (8, 'Scorpio', '2000-10-23', '2000-11-21');
INSERT INTO signs VALUES (9, 'Sagitarius', '2000-11-22', '2000-12-21');
INSERT INTO signs VALUES (11, 'Aquarius', '2000-01-20', '2000-02-18');
INSERT INTO signs VALUES (12, 'Pisces', '2000-02-19', '2000-03-20');
INSERT INTO signs VALUES (13, 'Aries', '2000-03-21', '2000-04-19');
INSERT INTO signs VALUES (10, 'Capricorn', '2000-12-22', '2000-12-31');
INSERT INTO signs VALUES (14, 'Capricorn', '2000-01-01', '2000-01-19');

zodiac=# select * from signs where '2000-03-18' between startdate and enddate;
 id |  sign  | startdate  |  enddate
----+--------+------------+------------
 12 | Pisces | 2000-02-19 | 2000-03-20
(1 row)

zodiac=# select * from signs where '2000-01-08' between startdate and enddate;
 id |   sign    | startdate  |  enddate
----+-----------+------------+------------
 14 | Capricorn | 2000-01-01 | 2000-01-19
(1 row)

zodiac=# select * from signs where '2000-12-28' between startdate and enddate;
 id |   sign    | startdate  |  enddate
----+-----------+------------+------------
 10 | Capricorn | 2000-12-22 | 2000-12-31
(1 row)

Basically, take whatever date you have and change the year to 2000 and this should work.

Tracer

Or you could try something like

WHERE MONTH(date_field)=X AND DAY(date_field) BETWEEN '01' AND '20'

fabiuz

:queasy: sorry.... I think to be a little bit too tired... at last it seems I was able to get zodiac signs from date of bird without any additional table or query. I do something like this:


CASE 
   WHEN date_format(dob,'%m%d') BETWEEN '0321' AND '0420'
          THEN 'Aries' 

[...]

   WHEN date_format(dob,'%m%d') BETWEEN '1222' AND '1231'
          THEN 'Capricorn' 

WHEN date_format(dob,'%m%d') BETWEEN '0101' AND '0120'
      THEN 'Capricorn' 

ELSE '' 
END sign,

Hope this helps!

Thanks a lot for suggestions