CODEWITHSUNDEEP
sqlsql-servert-sql
bmsqldev
bmsqldev

Reputation: 2735

Conversion failed when converting character string to smalldatetime data type in sql server (Excluding the Static Values)

I have a table storing starttime and endtime.

I Got

Conversion failed when converting character string to smalldatetime data type.

Error while selecting below query using that table.

select CAST(substring(CONVERT(varchar,convert(smalldatetime,o.StartTime),114), 1,2) as int) 
from TimeTable O

In analysis , I found out that Few Records with Starttime as '9:30 PM' causing the error. ( Other Records having '09:30 PM' as start-time). I have excluded '9:30 PM' Records and query executed successfully.

The Problem Here is ,

If I explicitly specify the value in select statement as below,

SELECT CAST(substring(CONVERT(varchar,convert('9:30 PM',o.StartTime),114), 1,2) as int)

I didn't get any error.

But, when I select from the table I got the error. can Anyone help with this?

Upvotes: 1

Views: 27993

Answers (1)

Karthik Venkatraman
Karthik Venkatraman

Reputation: 1657

To what output your are actually looking for?

If you want the values like '9:30 PM' or '09:30 PM' to be converted as Time format, you can go ahead with the below one

SELECT CAST(CONVERT(VARCHAR(8), '9:30 PM', 108)AS TIME)
SELECT CAST(CONVERT(VARCHAR(8), '09:30 PM', 108)AS TIME)

Else if you want it to be stored in datetime or smalldatetime

then this will work for you

SELECT CAST(CONVERT(VARCHAR(8), '9:30 PM', 108)AS DATETIME)
SELECT CAST(CONVERT(VARCHAR(8), '09:30 PM', 108)AS DATETIME)

Upvotes: 4

Related Questions