I have a column stocked as a nvarchar(50) containing dates of this format ('yyyy/mm/dd'), i'd like to convert it to date so i can do some dateadds to make further comparaisons.
Nothing that i've seen so far has been working (was a lot about casts and converts) and always resulted in stuff like that :
"Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
Most likely you have data that cannot be converted properly. You can use a query like below to see the problem data( this works in SQL server 2012 and above)
declare @tableName table(stocked nvarchar(50));
insert into @tableName values
('2017/12/31'),('2017/11/11'),('2017/11/13'),('2017/13/11');
SET DATEFORMAT ymd;
select
stocked,
TRY_PARSE(stocked as datetime)
from @tableName
where TRY_PARSE(stocked as datetime) is NULL;
Use
ISDATE ( expression )
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
Basicly i had an excel table that i inserted into SQL server 2014, didn't manage do convert the data to date at the insertion but i guess it would be less of a pain working on the way of insertion than convert it later on
