Coverting varchar to numeric

I have a column in a table that has a varchar datatype, how do I convert it
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
--
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.comThe CAST() function allows you to convert between data types. Books-on-line
has several examples.
--
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com

Coverting varchar to numeric

I have a column in a table that has a varchar datatype, how do I convert it
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.com
The CAST() function allows you to convert between data types. Books-on-line
has several examples.
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com

Coverting varchar to numeric

I have a column in a table that has a varchar datatype, how do I convert it
to a numeric so I can use the sum function?
OR
Is there a way to add numbers with a varchar datatype?
Sebastian Cavignac
Network Administrator
Salt River Materials Group
928.639.8095
scavignac@.hotmail.comThe CAST() function allows you to convert between data types. Books-on-line
has several examples.
--Brian
(Please reply to the newsgroups only.)
"Sebastian Cavignac" <SebastianCavignac@.discussions.microsoft.com> wrote in
message news:71A7FC6F-3632-411D-92D5-2F455D9E8A76@.microsoft.com...
>I have a column in a table that has a varchar datatype, how do I convert it
> to a numeric so I can use the sum function?
> OR
> Is there a way to add numbers with a varchar datatype?
> --
> Sebastian Cavignac
> Network Administrator
> Salt River Materials Group
> 928.639.8095
> scavignac@.hotmail.com

Coverting Int to DateTime Recommendation?

Friends,

I have a database that I am in the process of converting to a new application. All the dates are currently stored as an numeric YYYYMMDD format. I am hoping someone can suggest the best way of dealing with these in SQL Server. Should I convert them to DateTime formats for example, and what is the best way to do this?

Any input would be greatly appreciated.

J.H.

yes you should do it.

the best way?

this is how I would do it -

create function YYYMMDDToDate(@.date int)
returns datetime
as
begin
declare @.datestring char(8)
set @.datestring = cast(@.date as char(8))
return convert(datetime, substring(@.datestring,1,4) +'-' +
substring(@.datestring,5,2) +'-' +
substring(@.datestring,7,2), 127)
end
go
alter table affectedtable add newDateColumn datetime;
go
update affectedtable
set newDateColumn = dbo.YYYMMDDToDate(oldDateColumn);
alter table affectedtable drop column oldDateColumn;
exec sp_rename 'dbo.affectedtable.newDateColumn', 'oldDateColumn', 'COLUMN';

|||

The trick is that MSSQL recognizes dates in YYYYMMDD format unambigously. So may just convert it twice as:

select cast(cast(20060104 as varchar) as datetime)