Search code examples
sqlsql-serverconverters

How to convert nvarchar having yyyymm values to date?

I have a table which has a column 'performance month'. The data type of this column is nvarchar(255) and the values are starting from '202001' to '202012'. How can I select last 5 month data from this table?

Solution

  • The simplest way is:

    select convert(date, performance_month + '01')

    The standard string representation for dates is YYYYMMDD.

    For data from the last five months, you can simply use:

    performance_month >= format(dateadd(month, -5, getdate()), 'yyyyMM')

    That is, don't convert the string to a date. Convert the date to a string -- this helps the optimizer because it can "see" performance_month without the cloud of function calls.