error to convert datatype varchar to float

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 2005 Forums

Transact-SQL (2005)

error to convert datatype varchar to float

Author

Topic

jyoshna
Starting Member

1 Post

Posted - 2012-06-12 : 07:09:11

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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-12 : 07:34:04

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.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-06-12 : 08:18:28

use this

select max(col*1.0) from table

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-06-12 : 08:43:11

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.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2012-06-12 : 10:12:02

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-06-20 : 05:35:05

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

Hermosavnv
Starting Member

2 Posts

Posted - 2013-08-12 : 20:08:18

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources