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

Cast from string to date

I created an SSIS package that pulls in legacy data from a DB2 AS400. There is an In Date field that is stored on the AS400 as OdbcType.Date or DATE. However, when I use SSIS to pull in the data, SQL wants it to be a string so I gave up and let SQL have it's way.

Now in my SELECT statement, I have this:

SELECT TLMST.TLNUMBER, TLMST.DOGNAM, BRDMST.BRDesc, TLMST.INDT
FROM TLMST INNER JOIN
BRDMST ON TLMST.BRDCOD = BRDMST.BreedNumber
WHERE (TLMST.INDT >= @.startdate) AND (TLMST.INDT <= @.enddate)
ORDER BY TLMST.INDT, TLMST.TLNUMBER

Because I see the dates stored in TLMST are yyyy-mm-dd format, this is not working. It is not pulling any records. I tried to use the CAST statement but I keep getting errors about casting from a string to a date and data overflow errors.

Should I go back and re-do the SSIS or is there a way to pull the records for this? Thanks for the information

Do a Conversion task in SSIS, with either chopping the values from the string to create a vlid date like yyyymmdd or use the format function with that.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||When you create your source in your data flow, what is the data type SSIS has set for the column. You can find this out by right clicking on the source and selecting advanced editor and then selecting the input and output properties. In there you can expand the input tree to find the data type for the date column.|||The advanced editor is showing the data type as string, however on the AS400 it is of type OdbcType.Date. But that is on the AS400 side which I am not too familiar with.|||In which case follow Jens advice to split the date passed and build a string that is of the correct format