sql选择当天发生的事情

I have columns:

我有专栏:

code     unique          date                     info      name
a123     adfgh           2014-07-14 14:15:00      test      user1
a124     aklgp           2014-08-12 09:15:00      test      user1
a175     opllp           2014-08-12 11:15:00      test      user2

And i would like get all records where unique = $variable but in that day. Exmpl: if my unique = 'aklgp' i would like see both

我希望获得所有记录,其中unique = $ variable但在那一天。例如:如果我的唯一='aklgp',我想看到两者

a124     aklgp           2014-08-12 09:15:00      test      user1
a175     opllp           2014-08-12 11:15:00      test      user2

becouse both records are in same day? Is it posible?

因为两个记录是在同一天?它可以吗?

I play with DATEDIFF but that not good for my

我玩DATEDIFF但对我不好

3 个解决方案

#1

Not sure this is enough, but the idea might be something like that.

不确定这是否足够,但这个想法可能就是这样。

cast([date] as date)

will take the "date" part of your datetime (hoping this is a datetime type), which will give you... a day.

将采用日期时间的“日期”部分(希望这是一个日期时间类型),这将给你......一天。

select * 
from yourTable
where cast([date] as date) IN (select cast([date] as date)
                               from yourTable
                               where [unique] = 'aklgp')

of course, if you have many aklgp on different days, you will get more datas. But unique should be... unique, no ?

当然,如果你在不同的日子里有很多aklgp,你将获得更多的数据。但唯一的应该是......独一无二,不是吗?

See SqlFiddle

EDIT

As mentioned by Bulat, you could also use EXISTS

如Bulat所述,您也可以使用EXISTS

SELECT * from yourTable t1
where exists (select null
              from yourTable t2
              where cast(t1.date as date) = cast(t2.date as date)
              and t2.[unique] = 'aklgp')

#2

May be this too help you,

也许这对你有帮助,

DECLARE @UNIQUE VARCHAR(20) 
SET @UNIQUE = 'AKLGP'

SELECT  * FROM [TABLE] 
WHERE   CAST([DATE] AS DATE) = (SELECT CAST([DATE] AS DATE) FROM [TABLE] WHERE [UNIQUE] = @UNIQUE )

#3

I wouldn't know a solution using only SQL. Maybe a solution in PHP could offer some help:

我不知道只使用SQL的解决方案。也许PHP中的解决方案可以提供一些帮助:

$query = "SELECT * FROM yourTable WHERE unique = 'aklgp'";
$result = mysql_query($sql) or die(mysql_error());
$row = mysql_fetch_row($result);

$year = date_format($row['date'], 'Y');
$month = date_format($row['date'], 'm');
$day = date_format($row['date'], 'd');

$query = "SELECT * FROM yourTable 
WHERE  (DATEPART(yy, date) = $year
AND    DATEPART(mm, date) = $month
AND    DATEPART(dd, date) = $day)";

#1