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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page

Hermosavnv
Starting Member

2 Posts

Posted - 2013-08-12 : 20:08:18
unspammed
Go to Top of Page
   

- Advertisement -