Fetching Data based on the day

2002-03-27 Thread Soheil Shaghaghi 

Hello everyone.
I am trying to extract the data from MySQL based on the month, and day of
the item, and post them on a page.
Something similar to Today in History
Here is a partial list of my table:
CREATE TABLE ANNOUNCEMENT (
  AnnouncementID bigint(20) unsigned DEFAULT '0' NOT NULL,
  Title varchar(250),
  DateShown date DEFAULT '-00-00' NOT NULL
  KEY AnnouncementID (AnnouncementID),
  KEY ID (AnnouncementID)
);
Can anyone please tell me what I need to do?
Here is what I have so far:
SELECT AnnouncementID,Title,DateShown FROM ANNOUNCEMENT WHERE
DateShown=NOW() ORDER BY DateShown DESC

The problem is that it matches the list to the entire date (including the
year), but I only want to look at the month, and the day, when fetching the
data.

Thanks so much.


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Fetching Data based on the day

2002-03-27 Thread Rodney Broom 

From: Soheil Shaghaghi [EMAIL PROTECTED]


 SELECT AnnouncementID,Title,DateShown FROM ANNOUNCEMENT WHERE
 DateShown=NOW() ORDER BY DateShown DESC

 ...I only want to look at the month, and the day

  SELECT AnnouncementID,Title,DateShown
FROM ANNOUNCEMENT
   WHERE substring(DateShown,6,5) = substring(NOW(),6,5)
   ORDER BY DateShown DESC


Try these for an explination:

  select substring('2001-02-03 04:05:06', 6,5) as mon_day,
 substring('2001-02-03 04:05:06', 6,2) as mon,
 substring('2001-02-03 04:05:06', 9,2) as day;

  select substring(now(), 6,5) as mon_day,
 substring(now(), 6,2) as mon,
 substring(now(), 9,2) as day;



---
Rodney Broom
Programmer: Desert.Net

sql



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Fetching Data based on the day

2002-03-27 Thread Egor Egorov 

Soheil,
Wednesday, March 27, 2002, 6:45:38 PM, you wrote:

SS I am trying to extract the data from MySQL based on the month, and day of
SS the item, and post them on a page.
SS Something similar to Today in History
SS Here is a partial list of my table:
SS CREATE TABLE ANNOUNCEMENT (
SS   AnnouncementID bigint(20) unsigned DEFAULT '0' NOT NULL,
SS   Title varchar(250),
SS   DateShown date DEFAULT '-00-00' NOT NULL
SS   KEY AnnouncementID (AnnouncementID),
SS   KEY ID (AnnouncementID)
SS );
SS Can anyone please tell me what I need to do?
SS Here is what I have so far:
SS SELECT AnnouncementID,Title,DateShown FROM ANNOUNCEMENT WHERE
SS DateShown=NOW() ORDER BY DateShown DESC

SS The problem is that it matches the list to the entire date (including the
SS year), but I only want to look at the month, and the day, when fetching the
SS data.

Look at the date and time functions, such as DAYOFMONTH(), YEAR() and so
on:
   http://www.mysql.com/doc/D/a/Date_and_time_functions.html

SS Thanks so much.





-- 
For technical support contracts, goto https://order.mysql.com/
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php