query Syntax

I have the following query that pulls distinct filedate from my table. I need to add a column DisplayDate to use in a dropdown so an example would be 1016-09-01 and in my matching column I want Sep 2016


SELECT DISTINCT [filedate]
FROM [dbo].[NC_ProviderInfo]
ORDER BY [filedate] DESC

Open in new window

mburk1968Asked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Try this... If you are using SQL below SQL 2012.

--

SELECT DISTINCT [filedate] , LEFT(DATENAME(MONTH, [filedate]),3) + ' ' + CAST(YEAR([filedate]) AS VARCHAR(4)) DisplayDate
FROM [dbo].[NC_ProviderInfo]
ORDER BY [filedate] DESC

--

Open in new window

0
 
Kelvin SparksCommented:
Try

SELECT DISTINCT [filedate], CONVERT(CHAR(3), DATENAME(MONTH, [filedate]) + ' ' + YEAR([filedate]) as DisplayDate
FROM [dbo].[NC_ProviderInfo]
ORDER BY [filedate] DESC

Kelvin
0
 
Pawan KumarDatabase ExpertCommented:
Try below, Format Function , Works with SQL 2012 & above !!

SELECT DISTINCT [filedate] , FORMAT([filedate], 'MMMM yyyy') DisplayDate
FROM [dbo].[NC_ProviderInfo]
ORDER BY [filedate] DESC

Open in new window

0
 
mburk1968Author Commented:
receiving the following  
Msg 245, Level 16, State 1, Procedure dbo.qry_ActiveMonths, Line 15 [Batch Start Line 0]
Conversion failed when converting the nvarchar value 'July ' to data type int.

SELECT DISTINCT [filedate], CONVERT(CHAR(3), DATENAME(MONTH, [filedate]) + ' ' + YEAR([filedate]) as DisplayDate
FROM [dbo].[NC_ProviderInfo]
ORDER BY [filedate] DESC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.