Another common SQL statement
The date format of the DateTime field (field name birth) of the database member table is YY - mm - DD
Now I want to list a member of the birthday near 10 days, how to write the SQL statement?
It's not easy, it's easy to think
Select * from Table Where Birth Between
Getdate () and
Getdate () 10
GetDate () returns the current time, is a DateTime type
Oh, there is a vulnerability, Birth should be converted to the current year, otherwise it can't come out.
Select * from table
WHERE
Dateadd (year,
Year (GetDate ()) -
Year (Birth), Birth)
Between getdate () and getdate () 10
DATEADD () adds the specified amount of time, such as 10 minutes, 3 days, etc., returns a DateTime type at a given time.
YEAR is the year, INT type returns a given time.
Looks like a good, test, there is a problem, if the current date is 2004-12-27, this algorithm will ignore those who have lost 2005-1- *
Continue to modify, the main problem of the above algorithm is in DateAdd, 1981-12-29 and 1981-1-1 births of people ADD value should be different, this value is a variable, then on this value Year, Year (GetDate ()) - Year (birth-10) is just fine, so
Select * from table
WHERE
Dateadd (year,
Year (GetDate ()) -
Year (Birth-10), Birth)
Between getdate () and getdate () 10
Test it, ok, perfect.
It seems that even if it seems such a simple statement, there is still a trap. The trap of this topic is the problem of the New Year.
Attachment:
Use Tempdb
Go
Create Table STD
(std_id varchar (4), birth datetime)
Go
INSERT INTO STD
SELECT '0001', '20010101' Union All
SELECT '0002', '1991129' Union All
SELECT '0003', '19810104' Union All
SELECT '0004', '19720105'
Go
SELECT * from from std
WHERE
Dateadd (year,
Year (GetDate ()) -
Year (Birth-10), Birth)
Between getdate () and getdate () 10
