float column and half days - SQL Server Forums

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)

float column and half days

Author

Topic

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2009-09-16 : 05:46:09

Hi,

I have a 'float' column in my sql db called Number_Days. The column can be in whole number format i.e. 1.0, 5.0, 8.0 etc... or else in half days formats i.e. 2.5, 1.5, 8.5 etc..

I now need to return only the values which have a half day format i.e. 2.5. Any idea how i can do this.

Thanks for any help...

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-16 : 05:56:14

SELECT Number_Days FROM [YOURTABLE] WHERE Number_Days LIKE '%.5'

Hope can help...but advise to wait pros with confirmation...

kieran5405
Yak Posting Veteran

96 Posts

Posted - 2009-09-16 : 06:00:21

quote:
Originally posted by waterduck

SELECT Number_Days FROM [YOURTABLE] WHERE Number_Days LIKE '%.5'

Hope can help...but advise to wait pros with confirmation...

of course!!! how did i miss that!! thanks!!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-16 : 06:02:40

That needs to do a convert to varchar first, because you cannot use like and '%' on numeric datatype
SELECT Number_Days FROM [YOURTABLE] WHERE convert(varchar(10),Number_Days) LIKE '%.5'

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-16 : 06:35:47

or

ceiling(Number_Days)<>floor(Number_Days)

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-16 : 06:39:29

[code]DECLARE @fun TABLE(col1 float)
INSERT INTO @fun SELECT
2.5 UNION SELECT
3.5 UNION SELECT
4.5 UNION SELECT
6.5 UNION SELECT
1.0 UNION SELECT
2 UNION SELECT
6 UNION SELECT
8 UNION SELECT
4 UNION SELECT
5

SELECT * FROM @fun where col1 LIKE '%.5'[/code]
works?

Hope can help...but advise to wait pros with confirmation...

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-16 : 06:47:26

OK! It works but I think there happens an implicit convert.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources