'Date in MON-YYYY format to Text format

How do I convert date format field to MON-YYYY format.

I have date 12-06-2014 and I want to compare it with JUN-2014.

sqlsql-serverdate


Solution 1:[1]

Here is a link that explains everything about date format: Date Format

Your solution is probably the following (MySQL version):

UPPER(DATE_FORMAT(yourDate, '%b-%Y'))

And for SQL Server:

UPPER(SUBSTRING(DATENAME(MONTH, yourDate), 1, 3) + '-' + CAST(DATEPART(YEAR, yourDate) AS VARCHAR(4)))

Hope this will help you

Solution 2:[2]

SQL supports a number of different date formats, but does not support non-numeric based date formats. I would strongly suggest finding a method which uses one of these formats as suggested the Microsoft SQL documentation.

Alternatively, if it is absolutely necessary that you use this format, then the following code can change any date format into the format you have requested.

declare @date date = '2014-01-01'
select 
case when DATEPART(mm,@date) = 1 then 'JAN'
when DATEPART(mm,@date) = 2 then 'FEB'
when DATEPART(mm,@date) = 3 then 'MAR'
when DATEPART(mm,@date) = 4 then 'APR'
when DATEPART(mm,@date) = 5 then 'MAY'
when DATEPART(mm,@date) = 6 then 'JUN'
when DATEPART(mm,@date) = 7 then 'JUL'
when DATEPART(mm,@date) = 8 then 'AUG'
when DATEPART(mm,@date) = 9 then 'SEP'
when DATEPART(mm,@date) = 10 then 'OCT'
when DATEPART(mm,@date) = 11 then 'NOV'
when DATEPART(mm,@date) = 12 then 'DEC' end 
+ '-' 
+ CONVERT(char(4),DATEPART(yy,@date)) as new_format

Solution 3:[3]

You could use something like the following to format the date and compare (SQL Fiddle):

SELECT * 
FROM
(
  SELECT 
    CONCAT(
      CONVERT(CHAR(3), CONVERT(DATE, MyDateField, 105), 0), 
      '-',
      DATEPART(YYYY, CONVERT(DATE, MyDateField, 105))
    ) AS DF
  FROM MyTable
) m
WHERE m.DF = 'JUN-2014';

Solution 4:[4]

You can use below for convert date formate.

select left(datename(mm,[Date]),3)
+'-' + cast( DATEPART(YYYY,[date]) as nvarchar(50))
from ArtistCalendar

Solution 5:[5]

You can use the DATENAME() function to get the name for a given month, which can be abbreviated like so:

SELECT LEFT(DATENAME(mm, GETDATE()),3)  -- Output: 'Sep'

You can append the year using: 

SELECT YEAR(GETDATE())

This should have to be converted to a string value to enable comparison with your supplied value. So you can do this:

SELECT LEFT(DATENAME(mm, GETDATE()),3) + '-' 
     + CAST(YEAR(GETDATE()) AS NVARCHAR(4)) AS InputDate  -- Output: Sep-2014

To check equality of a supplied value, e.g. Sep-2014:

SELECT LEFT(DATENAME(mm, GETDATE()),3) + '-' 
     + CAST(YEAR(GETDATE()) AS NVARCHAR(4)) AS InputDate,
     GETDATE() as FullDate,
     CASE WHEN LEFT(DATENAME(mm, getdate()),3) + '-' 
             + CAST(YEAR(GETDATE()) as nvarchar(4)) = 'SEP-2014' 
          THEN 'True' 
          ELSE 'False' 
    END as Equality

Ouptut

InputDate   FullDate                 Equality
----------------------------------------------
Sep-2014    2014-09-15 14:35:57.427  True

To use with your table:

DECLARE @compareDate AS NVARCHAR(10)
SET @compareDate = 'Jan-2014'  -- set this

SELECT LEFT(DATENAME(mm, [YOUR_COL]),3) + '-' 
     + CAST(YEAR([YOUR_COL]) AS NVARCHAR(4)) AS InputDate,
     [YOUR_COL]as FullDate,
     CASE WHEN LEFT(DATENAME(mm, [YOUR_COL]),3) + '-' 
             + CAST(YEAR([YOUR_COL]) as nvarchar(4)) = @compareDate
          THEN 'True' 
          ELSE 'False' 
    END as Equality
From [YOUR_TABLE]

Just replace [YOUR_COL] with your date column and [YOUR_TABLE] with the table that holds the data column.

Solution 6:[6]

The SQL Server syntax is:

UPPER(FORMAT(yourDate, 'MMM-yyyy'))

Solution 7:[7]

In Oracle SQL You can do something like this

TO_CHAR(column_name, 'MON-YYYY')

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Bobcat
Solution 3
Solution 4
Solution 5 Josh Crozier
Solution 6 d219
Solution 7 Dharman

Related Questions