CODEWITHSUNDEEP
sql-server
Amol Kendre
Amol Kendre

Reputation: 23

Convert datetime which is in varchar to datetime

This is my query

select cast('2014/07/30 13:57:38.893000' as datetime)

Above query returns an error message:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Can anyone help me?

Upvotes: 1

Views: 46

Answers (3)

ptilton
ptilton

Reputation: 132

The precision specified in the string can be handled by a Datetime2 which is why Datetime2 was added to SQL Server 2008:

select cast('2014/07/30 13:57:38.893000' as datetime2)

From the SQL Server Transact SQL Documentation:

Character length:
19 positions minimum (YYYY-MM-DD hh:mm:ss) to 27 maximum (YYYY-MM-DD hh:mm:ss.0000000)

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

Try this:

declare @s varchar(30)
set @s = '2014/07/30 13:57:38.893000'
select cast(LEFT(@s,LEN(@s)-3) as datetime)

SQL FIDDLE DEMO

Upvotes: 0

David
David

Reputation: 1774

That's too much precision for the parser to understand.

Just try it like this: 

select cast('2014/07/30 13:57:38.893' as datetime)

Upvotes: 0

Related Questions