Sorting with Datepart()
I want to add a birthday list of my users but i cannot get them to order properly, the year of birth is taken into account and I do not want that.
Answer:
To solve this you need to breakup your datefield using datepart, for instance if your query now reads:
SELECT employees.firstname, employees.middlename,
employees.lastname, employees.initials, employees.birthdate
FROM employees
WHERE employees.active = 1 AND employees.birthdate < '01-01-2001'
ORDER BY employees.birthdate
This query will sort your results on birthdate allright, but it has a result as if we had a group by year statement as well, we need to tell our database to break up the date and drop the year, the year is irrelevant.
To achieve that we will select a datepart of the birthdate as a temporary field name, in practice to get the months from a datefield use:
Datepart(mm,[birthdate]) AS MonthOfBirth
Now that we know this we can use this method to sort our records, this is exactly what we need for a birthday list but we also need a day to sort on, and we do not need the output of a month so our order by clause now becomes:
DatePart(mm,[birthdate]), DatePart(dd,[birthdate])
If we integrate this into our existing query it becomes:
SELECT employees.firstname, employees.middlename,
employees.lastname, employees.initials, employees.birthdate
FROM employees
WHERE employees.active = 1 AND employees.birthdate < '01-01-2001'
ORDER BY DatePart(mm,[birthdate]), DatePart(dd,[birthdate])
In this query i'm only requesting employees that are active and where the birthdate is before 01-01-2001 in this table. The two dateparts first order the results by month and then by day, i found that a group by month is not necessary but depending on your database you might want to add a group by.
You can use the datepart for alot of usefull things, try experimenting with it alittle, to get the year you use DatePart(yyyy,[birthdate])
Have fun playing with SQL commands !
Comments
Be the first to write a comment
You must me logged in to write a comment.