CASTING statement for a date

I am selecting older legacy data from an AS400 mainframe that we still use. I am fairly new to constructing T-SQL statements so I hope I am doing this correctly. There is a table from the AS400 that has been setup with a field for TMONTH, TDAY, and TYEAR. Instead of having one field for a date, for some reason years ago this was set up this way.

I now have this statement in my SELECT statement and it is not working:

WHERE (CAST(OWNR.TMONTH + '/' + OWNR.TDAY + '/' + OWNR.TYEAR AS DATETIME) >= @.startdate)

I am not getting a syntax error, however I am getting "Error Converting data type varchar to numeric. These fields on the AS400 are set up as numeric fields.

What do I need to do differently? Should I use a CONVERT instead and if so, how would I structure that statement.

Thanks for the help

SQL Server will assume if one value is numeric that there has to be done an addition rather than a concatenation. So you will have to CAST all the numerics to chars like that:

WHERE (CAST(CAST(OWNR.TMONTH AS VARCHAR(2))+ '/' + CAST(OWNR.TDAY AS VARCHAR(2)) + '/' + CAST(OWNR.TYEAR AS VARCHAR(2)) AS DATETIME) >= @.startdate)

Or something like that:

WHERE CAST((100*OWNR.TMONTH + OWNR.TDAY + OWNR.TYEAR*10000) AS DATETIME) >= @.startdate)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

You may want to have a look at the answer to question 2 that I received in my post for Questions on dates. Kenneth has provided a means for using an international date format that is simple.

Code is:

cast(@.year as char(4)) + right('0' + cast(@.month as varchar(2)),2) + right('0' + cast(@.day as varchar(2)),2)

which may be assigned to a date variable. You will need to ensure that you have error checking to ensure that the date is valid.

Regards,

Flavelle