• Register
0 votes
453 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 196 views
196 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
1 vote
1 answer 20 views
20 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 6 views
6 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 47 views
47 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 128 views
128 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
1 vote
1 answer 34 views
34 views
Problem: I am having only elementary knowledge about Sql Server and Azure. I have gained basic knowledge about Sql server and Azure from the available online material. I want to write a very basic query that should calculate the average, min, max, and count. ... this part of programming so unable to understand the above error. Kindly guide me in fixing above Sql Server and Azure related error.
asked Jun 6, 2020 Martin K 6.6k points
1 vote
1 answer 156 views
156 views
Problem: I am having good exposure to SQL. Currently I want to insert my data from one table to the another table but because of some unknown reason to me I am facing some sorts of issue it shows me below error: Numeric value out of range: 8115 [Microsoft][ODBC ... ,'') AS [TRIM(IngredCost)]  FROM file_5979bd211a3a9` Please help me with this issue. I want to insert all the records in one go.
asked May 23, 2020 Martin K 6.6k points
1 vote
1 answer 142 views
142 views
Problem: I have very basic knowledge in Sql query writing. I have tried to write the below query. SELECT '$'        + CONVERT(VARCHAR (6), Cast(Avg(TotalPrice) AS NUMERIC (6, 2))) AS 'Average Price',        '$'        + CONVERT(VARCHAR (6), Cast(Min( ... converting numeric to data type varchar". I am unable to find the root cause of above error. Kindly help me in fixing my above sql query.
asked May 23, 2020 Martin K 6.6k points