How to return a character string from a sql datepart using DATENAME

Question: I am using SQL Server 2012 date functions. I want to input a date e.g 17-Jun-1987   and create an output e.g Monday 17 June 2015.

I know that I can take the outputs and manipulate in the client application, but are there SQL Server functions that do the work?

Answer: You can achieve this aim by using the DATENAME function. It is SQL Server Date function used in Transact – SQL .

These three statements are examples of how to extract a character string from a datepart based on the input date.In this example, we ar using GETDATE() , but any valid input date will work.

select datename(dw,getdate())

 --Monday

 select datename(month,getdate())

 --December

 select datename(year,getdate())

 --2015

Once these values are extracted they can be manipulated into any order and concatenated with other string variables to create the required output.

I have my reservations about using transact-sql to format dates and generally prefer to output ISO 8601 dates passing onto presentation layer which use functions to create the required output.

The ISO 8601 is an international standard for representing dates and times using numbers. If you'd like to learn more about these standards and how to apply to t-sql , read How to use ISO-8601 for date and datetime - SQL Server DBA

Read More on SQL dates

SQL Server – Datetime date range , AS400 and OPENQUERY - SQL ...

SQL 2012 datetime rounding issue - SQL Server DBA

Arithmetic overflow error and isdate sql - SQL Server DBA