Disallowed implicit conversion - CONVERT function

[marcie]

I'm having a problem using ASP to access some fields in SQL 6.5. The error I'm getting is: error '80040e07' Disallowed implicit conversion from datatype 'int' to datatype 'char'. It also says to use the CONVERT function, but I can't find any information on this function (such as syntax). I've tried changing some of the fields' datatypes to workaround this problem, but there are some fields that I just can't change. Anyone know where I can find out how to use CONVERT?

 

[Guest_imported]

<br>
CONVERT (data_type[(length)], expression [, style])<br>
<br>
data_type = the target system-supplied data type.<br>
<br>
length = optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types<br>
<br>
expression = any valid Microsoft® SQL Server™ expression. <br>
<br>
style =the style of date format you want when converting datetime or smalldatetime data to character data or the string format when converting float, real, money, or smallmoney data to character data. <br>
<br>
Hope this is enough info, for you. If you need some examples, just write back, and I will try to put osmething together for you )<br>
<br>
take care<br>
<br>

 

[Guest_imported]

<br>
CONVERT (data_type[(length)], expression [, style])<br>
<br>
data_type = the target system-supplied data type.<br>
<br>
length = optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types<br>
<br>
expression = any valid Microsoft® SQL Server™ expression. <br>
<br>
style =the style of date format you want when converting datetime or smalldatetime data to character data or the string format when converting float, real, money, or smallmoney data to character data. <br>
<br>
Hope this is enough info, for you. If you need some examples, just write back, and I will try to put osmething together for you )<br>
<br>
take care<br>
<br>

 

[clydejones]

Hello,

I am trying to write a stored procedure where I am passing a date value to it, but my query must find the Date and Time vaules of a DateTime Field. If I put the Date value in the SQL statement I get a return of all those dates, but when I put a time value in the SQL statement that I know is in the database, I do not get the return I am expecting (Actually I don't get anything). I've tried combining two variables (one of the date value and one of the time value) and adding that to the SQL statement. No matter what time I assigned to the time variable, I still got the same results. How Do I combine the date value and time value to get the record(s) that match(es) that date and time?

PS: Also tried making the time value a timestamp Data type, but had problems with the convert function. Any help or suggestions would be appreciated.

Thanks,
Clyde

 

[redlam]

Hi Clyde -
If you are trying to test for time values in a datetime field, I believe you'll need to convert the datetime datatype like this:

select *
from table
where convert(varchar,datetime_field,108) = '17:33:45'

 

[tlbroadbent]

Clyde,

First, it would be very helpful to have your question posted to a new thread. Enough said. ;-)

Datetime columns in SQL give people fits until you understand what you are dealing with and the functions provided to help. I suggest reading the &quot;Cast and Convert&quot; and &quot;Functions&quot; topics in SQL Books Online (BOL). Pay special attention to date functions.


Here are a few examples. Assume I have a table (tbl) with a datetime column (dt) and want to Select records based on the datetime column.

Example 1: Select all record occurring on a specific date regardless of the value of the time component.
Select * From tbl Where convert(char(10), dt, 103) = '13/06/2001'

Note: When converting dates use the date style in the 3rd parameter position. In this case I used style 103 which is for British dates (dd/mm/yyyy).

Example 2: Select all records with a time between 5:00Am and 7:00Am, inclusive regardless of day.
Select * From tbl Where datepart(hour,dt) Between 5 And 7

Example 3: Select all records occurring at a specific time on a specific day.
Select * From tbl Where convert(char(20), dt) = 'jun 13 2001 12:03:04'

Or

Select * From tbl
Where convert(char(10), dt, 103) = '13/06/2001'
And convert(char(10), dt, 14) = '12:03:04'

Hope these examples are helpful. Hope I didn't create any syntax errors.
Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost