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 2005 Forums
 Transact-SQL (2005)
 error to convert datatype varchar to float		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jyoshna
Starting Member

1 Posts
Posted - 06/12/2012 :  07:09:11  Show Profile  Reply with Quote
I have a column name WBS varchar(100) – values willl be like 27, 27.1,27.2 ,27.2.1 etc…

now i need to get max wbs from that table, but the problem is, it is giving properly upto 0-9 it crosess like if 27.9, 27.10 then it is giving only 27.9 as max WBS? but actually max wbs is 27.10

please give me some suggestions. its very important to me.

this is my Query:
(select max(wbs) from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396)

i tried in following ways:
(select cast(isnull(max(WBS),0)as float) as wbs from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396)

(select Convert(float,(isnull(max(WBS),0)) as wbs from Schedule
where ParentScheduleID = 3577 –Schedule_Level = @SchLevel
and EntityID = 396)

even itried for decimal also. didnt find any result.

webfred
Flowing Fount of Yak Knowledge

Germany
8515 Posts
Posted - 06/12/2012 :  07:34:04  Show Profile  Visit webfred's Homepage  Reply with Quote
Is it possible to save 27.9 as 27.09?

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 06/12/2012 :  08:18:28  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
use this

select max(col*1.0) from table


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8515 Posts
Posted - 06/12/2012 :  08:43:11  Show Profile  Visit webfred's Homepage  Reply with Quote
quote:
Originally posted by madhivanan

use this

select max(col*1.0) from table


Madhivanan

Failing to plan is Planning to fail

values will be like 27, 27.1,27.2 ,27.2.1 etc…

No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

Ifor
Constraint Violating Yak Guru

476 Posts
Posted - 06/12/2012 :  10:12:02  Show Profile  Reply with Quote
As Webfred has mentioned, this is a bad way to hold the data.

You will have to split the string into its component integers.
If you have no more than 3 .'s , then try something like:

WITH R1
AS
(
	SELECT WBS, REVERSE(WBS) As rWBS
	FROM Schedule
	WHERE ParentScheduleID = 3577
		AND Schedule_Level = @SchLevel
		AND EntityID = 396
)
, R2
AS
(
	SELECT WBS
		,REVERSE(PARSENAME(rWBS, 1)) AS rWBS1
		,REVERSE(PARSENAME(rWBS, 2)) AS rWBS2
		,REVERSE(PARSENAME(rWBS, 3)) AS rWBS3
		,REVERSE(PARSENAME(rWBS, 4)) AS rWBS4
	FROM R1
)
SELECT TOP 1 WBS
FROM R2
ORDER BY
	CAST(rWBS1 AS int) DESC
	,CAST(rWBS2 AS int) DESC
	,CAST(rWBS3 AS int) DESC
	,CAST(rWBS4 AS int) DESC

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22461 Posts
Posted - 06/20/2012 :  05:35:05  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by webfred

quote:
Originally posted by madhivanan

use this

select max(col*1.0) from table


Madhivanan

Failing to plan is Planning to fail

values will be like 27, 27.1,27.2 ,27.2.1 etc…

No, you're never too old to Yak'n'Roll if you're too young to die.

I forgot to note that. May be start with

select max(replace(col,'.','')*1.0) from table



Madhivanan

Failing to plan is Planning to fail
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