CODEWITHSUNDEEP
sqlsql-server
djohnjohn
djohnjohn

Reputation: 65

Convert nvarchar to date

Trying to convert a datetime format column (example value: 12-11-2020 18:15:06) which is actually a nvarchar into this date format: yyyymmdd

This is what I tried so far but I'm getting the following error: enter image description here

What am I doing wrong?

Upvotes: 0

Views: 710

Answers (1)

anon
anon

Reputation:

There are many problems here.

  • Dates should not be stored as strings.

    • You lose the ability to perform any kind of date math or extract date parts.
    • You lose built-in validation (both invalid dates like February 31st and any garbage that doesn't even look like a date).
    • For example, we have no idea if 12-11-2020 is December 11th or November 12th, or if the data was entered consistently. Imagine a person from the US did some of the data entry and a colleague from Germany did the rest.

  • FORMAT() is the most expensive way to format a date (see this and this - these are articles about removing time altogether, but the overhead with FORMAT() is the same).

  • An index on MYDATE can't be used to satisfy this query, so you will do a full table scan every time, which will get worse and worse as the table grows.

You can perform your query in your given scenario without changing anything, but I highly recommend you fix the column and make sure data entry can't be arbitrary (use a date picker or calendar control so you dictate a consistent format).

If 12-11-2020 is December 11th:

WHERE TRY_CONVERT(date, MYDATE, 110) >= @DateVariable;

If 12-11-2020 is November 12th:

WHERE TRY_CONVERT(date, MYDATE, 105) >= @DateVariable;

Note that this still might not get the correct and logical results if some people thought they entered December 11th and others thought they entered November 12th.

You can see all the valid style numbers for CONVERT/TRY_CONVERT here.

Upvotes: 1

Related Questions