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 or convert varchar to money/datetime

Hello,
I have a dataset that is all varchar with leading and
trailing spaces (comes from a mainframe). I import this
data to a table that is all varchar (using DTS). I then
insert it to a table that has the correct datatype fields,
but I have to perform a conversion. I have been using
Cast(ltrim(rtrim(colx)) As datetime)
Cast(ltrim(rtrim(colx)) As money)
I seem to be getting the correct data with datetime, and
with money I get like 306.6900. Am I supposed to get a $
symbol for money? Or just decimal? So is cast the
correct operator here or should I use convert? If
convert - how do I convert money?
Thanks,
RonThere is no reason to store the dollar sign with the numeric value, nor is
there a need to use the MONEY data type. In fact, this can cause problems.
See http://www.aspfaq.com/2503 which, among other things, describes reasons
to use DECIMAL in favor of MONEY/SMALLMONEY.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3601c53611$48d3c830$a401280a@.phx.gbl...
> Hello,
> I have a dataset that is all varchar with leading and
> trailing spaces (comes from a mainframe). I import this
> data to a table that is all varchar (using DTS). I then
> insert it to a table that has the correct datatype fields,
> but I have to perform a conversion. I have been using
> Cast(ltrim(rtrim(colx)) As datetime)
> Cast(ltrim(rtrim(colx)) As money)
> I seem to be getting the correct data with datetime, and
> with money I get like 306.6900. Am I supposed to get a $
> symbol for money? Or just decimal? So is cast the
> correct operator here or should I use convert? If
> convert - how do I convert money?
> Thanks,
> Ron|||SQL Server stores data, not the presentation of data. The client application
is what is doing the
presentation of your data. for the money datatype, the values 306.6900 and 3
06.69 are the same.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ron" <anonymous@.discussions.microsoft.com> wrote in message
news:0b3601c53611$48d3c830$a401280a@.phx.gbl...
> Hello,
> I have a dataset that is all varchar with leading and
> trailing spaces (comes from a mainframe). I import this
> data to a table that is all varchar (using DTS). I then
> insert it to a table that has the correct datatype fields,
> but I have to perform a conversion. I have been using
> Cast(ltrim(rtrim(colx)) As datetime)
> Cast(ltrim(rtrim(colx)) As money)
> I seem to be getting the correct data with datetime, and
> with money I get like 306.6900. Am I supposed to get a $
> symbol for money? Or just decimal? So is cast the
> correct operator here or should I use convert? If
> convert - how do I convert money?
> Thanks,
> Ron|||Ron,
[money] values are numbers. 306.6900 is a number. The $ symbol
is not part of a money value, but just something that may be part of the
display of a number. SQL Server can convert money values to
strings with $ or with commas, using CONVERT with format codes
(see CAST AND CONVERT in Books Online).
The type of Cast(whatever as money) will definitely be [money]. If
you want to be absolutely sure, try
select cast(whatever as money) as onlyColumn
into #checktype
then look at the table structure for #checktype.
Steve Kass
Drew University
Ron wrote:

>Hello,
>I have a dataset that is all varchar with leading and
>trailing spaces (comes from a mainframe). I import this
>data to a table that is all varchar (using DTS). I then
>insert it to a table that has the correct datatype fields,
>but I have to perform a conversion. I have been using
>Cast(ltrim(rtrim(colx)) As datetime)
>Cast(ltrim(rtrim(colx)) As money)
>I seem to be getting the correct data with datetime, and
>with money I get like 306.6900. Am I supposed to get a $
>symbol for money? Or just decimal? So is cast the
>correct operator here or should I use convert? If
>convert - how do I convert money?
>Thanks,
>Ron
>