I'm trying to write a query and convert a nvarchar to a date. I've tried the below but keep receiving an error:

Conversion failed when converting date and/or time from character string.

cast(columnName as DATE) as castDate
convert(Date, columnName, 23) as convertDate

(nvarchar(max),null)
2021-12-30 02:22:24 UTC

Desired output:

2021-12-30

CodePudding user response：

Date and time data types don't support 3 letter abbreviations for timezones (such as 'UTC' here). As you only need to date, then I would suggest you simply take the 10 left most characters and then CONVERT/CAST the value. I use TRY_CONVERT here, as due to the decision to use nvarchar for the data type (not even touching on the fact that it's MAX in length) you could have bad dates:

TRY_CONVERT(date,LEFT(YourColumn,10))

Fortunately, yyyy-MM-dd is an unambiguous format for the date data type.

I do, however, strongly suggest you fix your design. nvarchar is not an appropriate data type for a date and time value, and certainly a MAX length value (suggesting that the value of the date is likely to be more than 4,000 characters in length) is completely wrong. Most likely you should be using a datetimeoffset here (or maybe just as datetime2 if all your values are UTC).

CodePudding user response：

@Larnu Thanks for pointing me in the right direction. I was able to use the below to get the desired output;

CAST(LEFT(columnName, 10) AS DATE) as 'newDate'