Comparing two fields with different datatypes

[vanuta]

I have a table that looks like this:

record vnd_nbr vnd_name srtinvnbr inv_dte srtinvamt

1 101535821 TRAVELITE 82498 11/31/1999 824.98
2 101535821 TRAVELITE 85666 12/1/1998 500.01
3 101535821 TRAVELITE 65577 5/8/2001 5820.01
4 101535821 TRAVELITE 95888 6/5/2000 1015358.21

I would like to write a query or stored procedure to query out anything that has the:
1. vnd_nbr = srtinvamt (record 4)
2. srtinvnbr = srtinvamt (record 1)
3. inv_dte = srtinvamt (record 3)

Each field has a different datatype, so I know i have to convert the fields into a similar format in order to find out if they are equal to each other. I'm just not sure how to go about doing this.

The fields are currently formatted like this:

1.srtinvamt = (money, Null)
2.vnd_nbr = (char(20),Null)
3.srtinvnbr = (char(30),Null)
4.inv_dte = (datetime, Null)

Any help would be appreciated.

 

[martynh]

There are basically 3 combinations of data type conversion in SQL Server;

1. Implicit conversions: That is, those data types that SQL Server will convert on-the-fly for you. Sometimes this is dependant on the data values, e.g. char(3) will go to into if the char column has integer data in it.

2. Explicit conversions: That is, those data types that you can convert only of you use the CAST or CONVERT functions.

3. Invalid conversions: That is, all the rest, usually where you have binary data that just won't sensibly go into 'conventional' data types.

If you look at this table, you will see that money data types, are remarkably inflexible unless you use the CONVERT/CAST functions. I'd guess, looking at you data that a combination of 'FLOOR' (to do the decimal truncation) and CONVERT/CAST should see you right.

Hope this helps

Martyn

In the SQL Server help under data types (I think) there is a big truth table that shows all of the data types and which combinations fall into the above categories.

 

[Allumo]

Try using something like:

where ltrim(rtrim(vnd_nbr)) = replace(ltrim(rtrim( str(srtinvamt,10,2))),'.','')