Conversion failed when converting character string to smalldatetime data type

Question

Problem :

I have the table storing starttime and endtime.

I am facing below error:

“Conversion failed when converting character string to smalldatetime data type.”

While selecting the 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 the Few Records with Starttime as '9:30 PM' causing a error. (Other Records having the '09:30 PM' as start-time). I have excluded the '9:30 PM' Records and query executed very successfully.

The Problem Here is as below ,

If I explicitly specify a 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 face any error.

But, when I select it from a table I got the error. Can somebody help me with this?

Answer 1

To which output your are actually looking for?

If you want the values like a '9:30 PM' or '09:30 PM' to be converted as the Time format, you can go ahead with a 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 value to be stored in a datetime or smalldatetime

Then below one 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)