SQL Server Portal

Recently, I was developing a report for one of my customers, who migrated their database from SQL Server 2005 to SQL Server 2012 Enterprise Edition. The database belongs to a legacy system , so I found couple of conversion issues, however the major issue I faced in developing the report was the date format. The required date format was Mon-yyyy and the data available in the table was in the format of yyyymm.

Before proceeding with the solution, let me create a sample to demonstrate the problem.

USE tempdb
GO
DROP TABLE tbl_Sample
GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] varchar(6)
)
GO
INSERT INTO tbl_Sample VALUES (1,'201401')
INSERT INTO tbl_Sample VALUES (2,'201402')
INSERT INTO tbl_Sample VALUES (3,'201403')
INSERT INTO tbl_Sample VALUES (4,'201404')
INSERT INTO tbl_Sample VALUES (5,'201405')
INSERT INTO tbl_Sample VALUES (6,'201406')
INSERT INTO tbl_Sample VALUES (7,'201407')
INSERT INTO tbl_Sample VALUES (8,'201408')
INSERT INTO tbl_Sample VALUES (9,'201409')
INSERT INTO tbl_Sample VALUES (10,'201410')
INSERT INTO tbl_Sample VALUES (11,'201411')
INSERT INTO tbl_Sample VALUES (12,'201412')
GO
SELECT * FROM tbl_Sample
GO

Solution 1 : Traditional Method
This is the old traditional method, which is compatible with SQL Server 2005 and above. In this method, you need to use many functions (LEFT, DATENAME, CONVERT, YEAR) to convert yyyymm date format to Mon-yyyy. Given below is the script.

USE tempdb
GO
SELECT
LEFT(DATENAME(MM,CONVERT(DATE,[Date]+'01')),3)
+ '-'
+ CONVERT(VARCHAR(4),YEAR(CONVERT(DATE,[Date]+'01'))) As [Mon-Year]
FROM tbl_Sample
GO

Solution 2 : New Method
This is the new method, which is compatible with SQL Server 2012 and above. In this script, I will use a newly shipped (in SQL Server 2012) function namely Format to reduce the line of script in order to convert yyyymm to Mon-yyyy. Given below is the script.

USE tempdb
GO
SELECT FORMAT(CONVERT(DATE,[Date]+'01'),'MMM-yyyy') As [Mon-Year]
FROM tbl_Sample
GO

Read Full Post »