如何使用像操作员一样找到生日（白天） [英] How to use like operator to find birthday (day)

问题描述

我有数据库。它有一个调用UserProfile的表。它包含列生日。

每天和每天我想找到用户的生日

例如用户生日

Saman 1990/08/23

Rajith 1987/05/08

Piumi 1989/08/23

kumara 1989/04/06





我想找到8月23日出生的用户（08）

I have database. It is having table which call UserProfile. It contain column birthday.
Each and everyday I want to find the birthdays of users
Eg User birthday
Saman 1990/08/23
Rajith 1987/05/08
Piumi 1989/08/23
kumara 1989/04/06


I want to find the users who are born on 23rd of August(08)

select *
from
UserProfile
where BirthDay like&'%/08/23'

但是没有用。

But is is not working.

推荐答案

如果我理解你的情况正确我建议不要使用字符列来存储日期。相反，它应该被定义为日期列。



当列是日期列时，很容易提取日期或月份。例如

If I understand your situation correctly I would recommend not using a character column for storing dates. Instead it should be defined as a date column.

When the column is a date column, it's easy to extract the day or the month. For example

SELECT *
FROM UserProfile
WHERE DATEPART(day, BirthDay) = 23
AND  DATEPART(month, BirthDay) = 8

但关键是要为列使用正确的数据类型。



如果你真的无法更改数据类型，那么你可以在解决方案中使用LIKE示例1或使用CONVERT函数将类型从varchar转换为date。如果您使用的日期格式是YYYY / MM / DD，那么类似下面的内容应该可以工作

But the key is to use proper data type for the column.

If you really can't change the data type, then you can use the LIKE example in solution 1 or use CONVERT function to convert the type from varchar to date. If the date format you use is YYYY/MM/DD then something like the following should work

SELECT *
FROM UserProfile
WHERE DATEPART(day, CONVERT(date, BirthDay, 111)) = 23
AND  DATEPART(month, CONVERT(date, BirthDay, 111)) = 8

但如上所述，使用日期列是正确的方法。

But as said, using date column is the proper way to go.

如果你愿意，你可以这样做，但你必须知道日期格式。您需要将日期转换为特定格式的字符串，而不是使用LIKE运算符。

You can do it, if you like, but you have to be aware of the date format. You need to convert the date to string in a specific format, than you can use LIKE operator.

select * from
(SELECT convert(varchar, getdate(), 111) as BirthDate) as T
where T.BirthDate like '%/09/08'

见日期格式： https://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/ [ ^ ]

在SQL-2012中，你手头上还有更多：https://msdn.microsoft.com/en-us/library/hh213505.aspx [ ^ ]

这篇关于如何使用像操作员一样找到生日（白天）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！