CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008date
srinagesh
srinagesh

Reputation: 31

unable to convert varchar to date in sql server

in my data a column called 'duedate' which is in the varchar(5) format has dates stored in the following format '20110725' for 25th july 2011, is there a way in which i can convert this to date format

i tried using

cast(duedate as datetime)

which did not work then i tried to convert it to bigint and then to datetime

cast( cast(duedate as bigint) as datetime)

which said

arithematic overflow error

Sorry for the confusion it was varchar(50) - typo error, and thanks a lot for the help ill try the things you guys mentioned

Upvotes: 3

Views: 2867

Answers (4)

Yuck
Yuck

Reputation: 50835

Try running this on your table to find the values that aren't dates:

SELECT duedate
FROM your_table
WHERE ISDATE(duedate) = 0;

Like Christian said, a VarChar(5) won't be able to hold the value you're expecting it to either.

Upvotes: 1

Christian Specht
Christian Specht

Reputation: 36421

If the column is really varchar(5), then it can't have values like '20110725', because that would be 8 characters.

Maybe the values were truncated when they were inserted?
'20110725' shortened to 5 characters becomes '20117', and converting that won't work.

I guess the problem is something like that, because as Mladen Prajdic already said, converting '20110725' to datetime works perfectly.

Upvotes: 5

RB.
RB.

Reputation: 37192

Please see this table of valid date-time conversion codes. The code you will need is 112 which converts from yyyymmdd format.

CONVERT (datetime, duedate, 112)

Upvotes: 4

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

how didn't it work? doing SELECT cast('20110725' as datetime) works perfectly for me.

Upvotes: 2

Related Questions