Convert string nvarchar(6) with format MMM-YY to date

Question

Post reply

Convert string nvarchar(6) with format MMM-YY to date

jguffey

SSC Eights!

Points: 850
More actions
March 29, 2016 at 9:05 am

#309512

Hello,
I have a column that is an nvarchar(6) and the data in the column is formatted like this:
Dec-15
Jan-16
Feb-16
I'd like to display the data like this:
December 2015
January 2016
February 2016
Is there a better way to get the desired output than doing something like this?
CASE
WHEN substring(column, 1, 3) = 'Jan'
THEN 'January' + ' 20'+ right(column, 2)
I hate hard coding the ' 20' but I just can't think of a better way to achieve the desired result right now.
Does anyone have any other suggestions?
Thanks for your time.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100942 More actions · Answer 1

SELECT DATENAME(MONTH, CAST('01-' + string AS date)) + SPACE(1) +

CAST(YEAR(CAST('01-' + string AS date)) AS varchar(4))

FROM (VALUES('Dec-15'),('Jan-16'),('Feb-16')) test_data(string)

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

With a cast to DATE in the middle

DECLARE @halfDate NVARCHAR(6) = 'Dec-15'

SELECT DATEname(MONTH, CAST('01-'+@halfDate AS DATE)) + ' ' + CAST(YEAR(CAST('01-'+@halfDate AS DATE)) AS VARCHAR(4))

Not sure if it's cleaner though.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jguffey SSC Eights! Points: 850 More actions · Answer 3

Thanks so much for the fast reply.

This works great, but I was curious what the significance of the literal '01-' was for. I'm a little confused by that part.

Thanks again!!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

The first of the month.

01-Jan-15 is a valid date. Jan-15 is not.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jguffey SSC Eights! Points: 850 More actions · Answer 5

Of course. I don't know why I didn't see that earlier.

Thank you both very much for your input.

Both solutions solve the issue so again thanks so much.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Just seems a little cleaner:

with ConvertDate as (

select cast('01-' + string as date) DateValue

from (values('Dec-15'),('Jan-16'),('Feb-16')) test_data(string)

)

select datename(month, DateValue) + ' ' + datename(year, DateValue) MonthYear from ConvertDate;

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

Lynn Pettis (3/29/2016)
Just seems a little cleaner:
with ConvertDate as (
select cast('01-' + string as date) DateValue
from (values('Dec-15'),('Jan-16'),('Feb-16')) test_data(string)
)
select datename(month, DateValue) + ' ' + datename(year, DateValue) MonthYear from ConvertDate;

I absolutely agree with formula consolidation. In that same vein...

SELECT ConvertDate = DATENAME(mm,ca.DT)+' '+DATENAME(yy,ca.DT)

FROM (VALUES('Feb-16'),('Jan-16'),('Dec-15')) testdata (String) --Out of order

CROSS APPLY (SELECT CAST('1-'+String AS DATE)) ca (DT)

ORDER BY ca.DT

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

CGZ Mr or Mrs. 500 Points: 522 More actions · Answer 8

Hello everyone!
The answers were really helpful, thank you! but i am facing a problem with language, i have strings in the format mmm-yy with months in spanish:
'ene-19' (enero = january 2019, doesn't work)
'feb-19' (febrero = february 2019, works fine)
'abr-19' (abril = april 2019, doesn't work)
...
I have tried with SET LANGUAGE and TRY_PARSE with USING 'es-ES' and nothing changed, i still can't get the right date.
Any ideas? Is there a way to do it?

Thanks in advance!

PS: I can't believe i'm writing in a post with answers from Jeff Moden :Wow:!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 9

CGZ - Thursday, February 7, 2019 8:51 PM
Hello everyone!
The answers were really helpful, thank you! but i am facing a problem with language, i have strings in the format mmm-yy with months in spanish:
'ene-19' (enero = january 2019, doesn't work)
'feb-19' (febrero = february 2019, works fine)
'abr-19' (abril = april 2019, doesn't work)
...
I have tried with SET LANGUAGE and TRY_PARSE with USING 'es-ES' and nothing changed, i still can't get the right date.
Any ideas? Is there a way to do it?
Thanks in advance!
PS: I can't believe i'm writing in a post with answers from Jeff Moden :Wow:!

You might be using the incorrect value when using SET LANGUAGE. This works:
--SELECT * FROM sys.syslanguages SET LANGUAGE EspaÃ±ol; --SET LANGUAGE Spanish; --This also works

SELECT mmmyy, TRY_CAST( '01-' + mmmyy as date) FROM (VALUES('ene-19'), ('feb-19'),('abr-19'))x(mmmyy)

SET LANGUAGE English
You can get the correct values from the syslanguages view.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

CGZ Mr or Mrs. 500 Points: 522 More actions · Answer 10

Luis Cazares - Friday, February 8, 2019 7:59 AM
CGZ - Thursday, February 7, 2019 8:51 PM
Hello everyone!
The answers were really helpful, thank you! but i am facing a problem with language, i have strings in the format mmm-yy with months in spanish:
'ene-19' (enero = january 2019, doesn't work)
'feb-19' (febrero = february 2019, works fine)
'abr-19' (abril = april 2019, doesn't work)
...
I have tried with SET LANGUAGE and TRY_PARSE with USING 'es-ES' and nothing changed, i still can't get the right date.
Any ideas? Is there a way to do it?
Thanks in advance!
PS: I can't believe i'm writing in a post with answers from Jeff Moden :Wow:!
You might be using the incorrect value when using SET LANGUAGE. This works:
--SELECT * FROM sys.syslanguages SET LANGUAGE EspaÃ±ol; --SET LANGUAGE Spanish; --This also works
SELECT mmmyy, TRY_CAST( '01-' + mmmyy as date) FROM (VALUES('ene-19'), ('feb-19'),('abr-19'))x(mmmyy)
SET LANGUAGE English
You can get the correct values from the syslanguages view.

Thank you Luis! It works perfect.
Sorry for the silly question, next time i will take a deeper look at the documentation.

Have a nice weekend!