Post reply

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

  • 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.

  • March 29, 2016 at 9:13 am

    #1868288

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • March 29, 2016 at 9:13 am

    #1868289

    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

  • March 29, 2016 at 9:24 am

    #1868291

    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!!

  • March 29, 2016 at 9:31 am

    #1868293

    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

  • March 29, 2016 at 9:37 am

    #1868295

    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.

  • March 29, 2016 at 10:07 am

    #1868305

    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;

  • April 9, 2016 at 10:01 pm

    #1870995

    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)

  • February 7, 2019 at 8:51 pm

    #2021458

    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:!

  • February 8, 2019 at 7:59 am

    #2021502

    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

  • February 8, 2019 at 11:11 am

    #2021532

    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!

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy