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

28 December,2015 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to return a character string from a sql datepart using DATENAME


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer