• Register
0 votes
570 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

Please log in or register to answer this question.

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 327 views
327 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, 2020 jwilliam 3.9k points
0 votes
1 answer 2 views
2 views
Problem: Conversion failed when converting the nvarchar value 'test' to data type int.How can I get solution of this?
asked 3 days ago sumaiya simi 16.2k points
1 vote
1 answer 37 views
37 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, 2020 morrisBson 3.2k points
0 votes
1 answer 81 views
81 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
0 votes
1 answer 12 views
12 views
I received an error message when converting data type varchar to numeric I imported data into VARCHAR column to check correct character types before moving into the destination table. I ran across some decimal values they could not CAST or CONVERT even though they ... values. I received the message "Error converting data type varchar to numeric.” How can I load these problems values?
asked Nov 24, 2020 miki 1.9k points
0 votes
1 answer 5 views
5 views
Problem: I was always of the impression that a query with no specified 'Order by' rule, would order this by the results by what was specified within your where clause.
asked Mar 14 ummesalma 25.2k points
1 vote
1 answer 216 views
216 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, 2020 Martin K 6.6k points
0 votes
1 answer 1 view
1 view
Question: How can I solve the error in the following procedure? CREATE PROCEDURE cards @salesman VARCHAR(10), @RCV10 INT, @RCV09 INT, @RCV15 INT, @GPRS15 INT, @RCV20 INT, @RCV25FTT INT, @RCV25 INT, @RCV31 INT, @RCV30 INT, @RCV35 INT, @RCV50 INT, @RCV55 INT, @SIM ... /2013' gives the following error: Msg 8114, Level 16, State 5, Procedure cards, Line 0 Error converting data type varchar to datetime
asked 2 days ago Atik03 10.1k points
0 votes
1 answer 6 views
6 views
Problem: Hi, any idea that what should I do to fix it…I am new in php.error converting data type nvarchar to datetime
asked Mar 22 Ifra 24.4k points