Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert varchar to float?

Author  Topic 

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-21 : 09:05:07
Good day!

I have this table with a field named: transdate,qty with a varchar datatype. Here comes the time that I need to sum up all values within the qty field and I need to convert the transdate to datetime datatype too.. Some values have decimal places like 5.50, etc in qty field. And some values in transdate have 1/29/2012, etc. How can I convert the qty field to float datatype without losing any piece of data? As well as the transdate field to datetime datatype too?


Thank you!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-21 : 09:24:37
Assuming that the data in both columns are in consistent formats, you can do the following:
SELECT
CAST(transdate AS date), -- or datetime
SUM(CAST(qty AS FLOAT)) -- or decimal(19,4) etc.
FROM
TheTable
GROUP BY
CAST(transdate AS date);
If you have the opportunity, you should consider changing the schema to use the correct data types. That would avoid the need for these types of casting and many other problems.
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-21 : 22:02:54
thank you James K!

Will this changed the datatype permanently without losing any piece of data?

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-22 : 08:02:40
No. The query I posted would only select the data as it exists from the table, convert it to the appropriate types and return you the result.

If you want to change the data types, use the alter table command.
-- these are only examples. Look up alter table on MSDN and 
-- make appropriate changes to fit your needs. Also, first
-- test on a development server.
ALTER TABLE TheTable ALTER COLUMN transdate DATE NOT NULL;
ALTER TABLE TheTable ALTER COLUMN qty FLOAT NOT NULL;
Go to Top of Page

adbasanta
Posting Yak Master

120 Posts

Posted - 2013-01-25 : 05:21:07
Thank you James K! Ill pay around this solution tonight!

-------------------------------------------------------------------------------------------------------
Learning MS SQL Server 2008
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 05:29:34
be careful in changing varchar field to date field. If you've date values existing in variety of formats it can cause issues. Make sure you convert all dates to unambiguos formats before you do datatype change

http://visakhm.blogspot.in/2011/12/why-iso-format-is-recommended-while.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -