SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Convert varchar to float?		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

adbasanta
Posting Yak Master

108 Posts
Posted - 01/21/2013 :  09:05:07  Show Profile  Reply with Quote
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
Edited by - adbasanta on 01/21/2013 09:07:08

James K
Flowing Fount of Yak Knowledge

1733 Posts
Posted - 01/21/2013 :  09:24:37  Show Profile  Reply with Quote
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

108 Posts
Posted - 01/21/2013 :  22:02:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1733 Posts
Posted - 01/22/2013 :  08:02:40  Show Profile  Reply with Quote
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

108 Posts
Posted - 01/25/2013 :  05:21:07  Show Profile  Reply with Quote
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

India
48076 Posts
Posted - 01/25/2013 :  05:29:34  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000