Search code examples
sqlsql-serverdateformatnvarchar

How to convert a nvarchar(255) to YYYY-MM-DD format?

I need help in writing a SQL statement to convert date column which is of type nvarchar(255) into a date type 'yyyy-mm-dd' format

I tried all the alternatives available across stackoverflow relating this topic but couldn't find a solution for my dataset.

The sample data:

Fri, 23 Oct, 2020
Mon, 19 Oct, 2020
Fri, 16 Oct, 2020
Tue, 20 Oct, 2020
Tue, 27 Oct, 2020

To date format:

2020-Oct-23
2020-Oct-19
2020-Oct-16
2020-Oct-20
2020-Oct-27

I am using SQL server 2014.

Solution

  • SQL Server as pretty flexible default rules for converting strings to dates. However, it doesn't recognize the leading day of week. So, if you remove that, it is easy enough:

    select convert(date, stuff('Fri, 23 Oct, 2020', 1, 5, ''))

    Or referring to a column:

    select convert(date, stuff(datecol, 1, 5, ''))

    This produces a date which you can then format however you like using either format() or convert(). However, I recommend keeping the format as YYYY-MM-DD. This works for range comparisons and for ORDER BY. And you really want dates to be represented as dates.

    Here is a db<>fiddle.