How to extract day, month and year from a date field in SQL Server database sql query.

Here is a method to extract day, month and year from date field in sql server sql database.

Say their is a table named employee_attendance with field attn_date

we will get the day , month and year separately from the date field

Here is the SQL statement

select emp_id,substring(conver(char,attn_date,103),1,2) dy,
          substring(conver(char,attn_date,103),4,2) mon,
          substring(conver(char,attn_date,103),7,4) yr
          from employee_attendance
          order by emp_id

Now look into the sql statement where day, month and year is extracted.

Lets start with day  substring(conver(char,attn_date,103),1,2)

convert function with third parameter 103 convert date to dd/mm/yyyy
format, so 19th april 2013 is converted to 19/04/2013 and char converts the date to string, because if
we do not convert character to string then we cannot use substring function onto it. substring function
actually extract the pattern from the date string.

substring('19/04/2013',1,2) --> 19
substring('19/04/2013',4,2) --> 94
substring('19/04/2013',7,4) --> 2013