从datetime提取信息以输入到SQL查询中

Im having a bit of a problem trying to figure out how to write a query i need.

我有一个问题试图找出如何编写我需要的查询。

I have a whole bunch of records in a mysql table, each record has a 'datecreated' column which is a datetime type and formatted like so: 0000-00-00 00:00:00

我在mysql表中有一大堆记录,每个记录都有一个'datecreated'列,它是一个日期时间类型,格式如下:0000-00-00 00:00:00

What i need to do is extract all records that are in the 11th month of this year, but they year needs to be taken from the servers date, so that when it ticks over into a new year, it wont still run the query from last year.

我需要做的是提取今年第11个月的所有记录,但是它们需要从服务器日期开始计算,这样当它进入新的一年时,它仍然不会运行上一个查询年。

Does this make sense at all? ... Im totally stumped how to get it going.

这有意义吗? ...我完全不知道如何让它继续下去。

Cheers,

2 个解决方案

#1

select
    yt.*
from
    yourtable yt
where
    month(yt.datecreated) = 11
    and year(yt.datecreated) = year(now())
;

#2

Ok so this is not hard tbh ;) you want only for month 11 (november)? this should work man:

好吧,这不是很难;)你只想要11个月(11月)?这应该是男人:

//connect to database
$current_year = date("Y"); //gets the current year according to server time

$query = "SELECT * FROM your_table WHERE datecreated LIKE '".$current_year."-11%'";
$result = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_array($result)){
    echo '<br />'; echo $row['column_id']; echo '<br />';
    }

change your_table to your table name and column_id to name of a column of whatever you want to print

it will print everything which was created in 2011-11 ... however when the server time changes to 2012 it wont print out anything until there will be some input in November .. is that what you want?

它将打印2011-11中创建的所有内容...但是当服务器时间更改为2012时,它将不会打印出任何内容,直到11月会有一些输入..这是你想要的吗?

hope it helps (:

希望能帮助到你 (:

#1

select
    yt.*
from
    yourtable yt
where
    month(yt.datecreated) = 11
    and year(yt.datecreated) = year(now())
;

#2