CODEWITHSUNDEEP
sql-serverstringdate
Bastien Morel
Bastien Morel

Reputation: 13

SQL server convert a column from string to date

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."

Upvotes: 0

Views: 1107

Answers (3)

Bastien Morel
Bastien Morel

Reputation: 13

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

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

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;

Upvotes: 1

Slawomir Cieslinski
Slawomir Cieslinski

Reputation: 635

Use

ISDATE ( expression )

Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.

Upvotes: 0

Related Questions