Mysql function Usage record (i)--datediff, CONCAT

So far, the individual to MySQL function has not been unified learning to use, are used to learn. And recently began to learn Linux, so in order to prevent the end of this period, the MySQL function encountered during this phase of forgetting, began to make a simple record here.

Yesterday before work friends let help look at a SQL, is about birthday reminders, the actual application is to achieve a day in advance to remind users of the birthday, and SQL is used to query the day after the birthday of users. In her original SQL used the concat, DateDiff function, these two functions I was not used before, so after solving the problem here to record the usage and ideas.

First, consider the definition and usage of the concat and DATEDIFF functions:

CONCAT (): Used to concatenate two or more strings. If any one of the arguments is NULL, the return value is null.

DATEDIFF (): Returns the number of days between two dates.

Second, look at the requirements:

Query out the relative day, the day after the birthday of users.

After clarifying the use of requirements and functions, we look at the idea:

1, since it is to check the day after the birthday of the user, then we can simply consider the date of the birthday than the current date of the day of the user, then the problem came, so that only the current year, the current month, the current date after the birth of the user, obviously does not meet, then another idea, assuming that all users are born , so that we get a birthday date larger than the current date of the day users can not be ^_^ ~

2, in 1 we can see that this seems to be no problem, but think about it, if the current date is December 31, the day after that (that is, January 1) Birthday of the user if you follow the 1 of the idea can query it? Obviously not. So we'll come back to the December 31 and January 1, do not think of the cross-year, yes, that is the year, in that case, we will all users birthday date year is replaced by the current year years after the year, so that the date between the days of 1 is not able to filter out the day after December 31 birthday users? ^_^ ~

Finally, let's look at the SQL implementation:

1. Replacement + combination Birthday Date:

Replace the birthday date year with the current year:

CONCAT (Now ()), Date_format (Birthday,'-%m-%d'))

Replace the birthday date year with the current year years after the year:

CONCAT (Now ())+1, date_format (Birthday,'-%m-%d' ))

2, after processing the birthday date and the current day period of days:

Ordinary:

DATEDIFF(CONCAT (Now ()), Date_format (Birthday,'-%m-%d'), Now ())

Cross-year:

DATEDIFF(CONCAT (Now ())+1, date_format (Birthday,' -%m-%d ')), now ())

3. Integration, query the date of the day after the birthday of the user information:

　　

SELECT *  from User WHERE    DATEDIFF (CONCAT (Now ()), Date_format (Birthday,'-%m-%d')), now ()) = 1 OR    DATEDIFF (CONCAT (Now ())+1, date_format (Birthday,'-%m-%d  ')), now ()) = 1

At last:

The DATEDIFF function has two parameters, the previous one larger than the next, the value >=0, the previous one hours, the value <=0

The record is complete!

Mysql function Usage record (i)--datediff, CONCAT