Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER – How to get short month name / weekday name from datetime’

I came across this question on multiple forums and it is being solved using multiple function such as DATENAME and LEFT function functions. However, lets solve it using FORMAT function only.
Let me create an example to explain it.

USE tempdb
GO
CREATE TABLE tbl_Sample
(
 [ID] INT,
 [Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-02-02')
INSERT INTO tbl_Sample VALUES (3,'2013-03-03')
INSERT INTO tbl_Sample VALUES (4,'2013-04-04')
INSERT INTO tbl_Sample VALUES (5,'2013-05-05')
INSERT INTO tbl_Sample VALUES (6,'2013-06-06')
INSERT INTO tbl_Sample VALUES (7,'2013-07-07')

Method 1 :
In this method, we will use DATENAME & LEFT function to achieve short name of month and weekday.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
       [ID]
     , [Date]
     , LEFT(DATENAME(MONTH,[Date]),3) AS [Short Month Name]
     , LEFT(DATENAME(WEEKDAY,[Date]),3) AS [Short Weekday Name]
FROM tbl_Sample
--OUTPUT

MonthAndWeekdayshortname1.1

Method 2 :
In this method, we will use ONLY FORMAT function (shipped in SQL Server 2012) to achieve short name of month and weekday.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
       [ID]
     , [Date]
     , FORMAT([Date],'MMM') AS [Short Month Name]
     , FORMAT([Date],'ddd') AS [Short Weekday Name]
FROM tbl_Sample
--OUTPUT

MonthAndWeekdayshortname1.2

Read Full Post »