Find Ordered Year, Month and Day From Date in SQL Server

Introduction

This article explains how to find year, month and day from date separately and how to order them.

If you have a column in which date has been entered but you don't want to fetch the complete date and want to get only the part of it that might be it's year, month or day then you can follow this article because here I explain how to do this.

YEAR

SQL provides a predefined function for finding the year. Now for example if you have a column named "age" then write this code to find it's year:

  1. select year(age) from birth  

Now suppose you want to get the result in ascending order then you might use this code:

  1. select year(age) from birth order by age 

But this code will not provide you the expected result because you are finding the year from the date but you are applying the order by on the complete column, so to get the right result you need to modify your code to this:

  1. select year(age) from birth order by year(age) asc 

MONTH

SQL provides a predefined function for month as well, now I'll show you how to use this.

  1. select month(age) from birth 

Now again for finding the result in a specific order you need to use order by and this time also it should be applied on the month of the column.

  1. select month(age) from birth order by month(age) asc 

DAY

To find the day, use this code:

  1. select day(age) from birth 

And for ordering use this code:

  1. select day(age) from birth order by day(age) asc