Converting nvarchar to date - getting conversion string to datetime error

0
votes

I have searched and tried everything to resolve this issue but no luck. I have a date (Start_Date) in 12/1/2017 format (datatype nvarchar) and I need to convert it to a date 2017-12-01 format in order to compare against another start date that is in 2017-12-01 format.

I tried 

UPDATE #temp2 
SET [start_date] = CONVERT(NVARCHAR(255), CONVERT(SMALLDATETIME,[start_date], 105))

but I get this error:

Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a smalldatetime data type resulted in an out-of-range value.

2
sqlsql-server
The best fix for this is: Don't store dates in varchar columns! - Joel Coehoorn
What DBMS are you using? - Caldazar
Can't reproduce. DECLARE @s nvarchar(255) SET @s = '12/1/2017' SELECT CONVERT(NVARCHAR(255),CONVERT(SMALLDATETIME,@s,105)) works fine. But why are you converting back to nvarchar again? Convert both values to datetimes and then compare them. - ADyson
smalldatetimes still have a time aspect to them, try converting to date - WhatsThePoint
I'm hazard a guess that you have a value that isn't confirming to your formatting; this is why it's a bad idea sotring a date(time) as a (n)varchar. What is the result of SELECT start+date FROM #temp2 WHERE TRY_CONVERT(smalldatetime,start_date,105) IS NULL AND start_Date IS NOT NULL;? - Larnu

2 Answers

0
votes

You can use cast() don't need to use style code :

select *
from #temp2 t
where cast([start_date] as date) = ?
0
votes

I would suggest:

update #temp2
    set [start_date] = CONVERT(NVARCHAR(255), TRY_CONVERT(SMALLDATETIME,[start_date], 105))
    where TRY_CONVERT(SMALLDATETIME, [start_date]) is not null;

This would update the values where the string represents a date, while preserving the values of the rest.