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
>