• Register
0 votes
312 views

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?

7 5 2
3,870 points

1 Answer

0 votes

Solution :

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)

 

9 7 4
38,600 points

Related questions

0 votes
1 answer 134 views
134 views
Problem : I don't understand why I am unable to insert this. I can't spot any problem. The error message is as below : Conversion failed when converting from a character string to uniqueidentifier. A GUIDs are the ... Server 2012 The columns are as below id        uniqueidentifier, myid      uniqueidentifier, friendid  uniqueidentifier, time1     datetime nullable, time2     datetime nullable
asked Jan 17 jwilliam 3.9k points
1 vote
1 answer 17 views
17 views
Problem : I am new to SQL. While trying to execute my JOIN query I am facing below strange error. “Conversion failed when converting the varchar value to data type int”. I am unable to figure out why am I getting above error. Can someone help me in fixing the error?
asked Mar 27 morrisBson 3.2k points
0 votes
1 answer 32 views
32 views
Problem : I was getting the following error when running a SQL the conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
asked Nov 12, 2019 peterlaw 6.9k points
1 vote
1 answer 68 views
68 views
Problem : I am very new to SQL. If I try to use the sp_executesq I am often facing below strange looking error: “Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.” I have spent more than two days on above error but still can not fix above sql related error. Please guide me in fixing above sql error.
asked May 12 Martin K 6.6k points