SQL转换问题 [英] SQL conversion problem

问题描述

我使用的是SQL Server 2008.有一个名为OTHours的字段，其数据类型为varchar。

我编写了以下SQL查询

选择*来自tblSalaryRecordCompliance Where SalarySTDT ='2014年3月1日'和CAST（OTHours AS INT）> 52

但它出现错误。一些字段值如12,28：45,0，-5等，其中28:45表示28小时45分钟

请帮助我如何解决它。

I use SQL server 2008. There is a field named OTHours and its datatype is varchar.
I have written the following SQL query
Select * from tblSalaryRecordCompliance Where SalarySTDT ='1-Mar-2014' AND CAST(OTHours AS INT)>52
but it arises error. some of the field value like 12, 28:45, 0, -5 etc. where 28:45 means 28 hours and 45 minute
Please help me how can I make a solution of it.

推荐答案

最好的解决方案是：不要使用varchar字段并尝试用它做任何数学，它只会导致痛苦。总是使用数字字段表示数字数据！



你可以这样做 - SQL有一个ISNUMERIC测试 - 但你打算用28:45这样的字段做什么？别理他们？把它们视为匹配？无与伦比？

并且ISNUMERIC为123.4返回1，所以你仍然必须扮演愚蠢的玩家：

The best solution is: don't use a varchar field and try and do any maths with it, it only leads to pain. Always use a numeric field for numeric data!

You can do it - SQL has a ISNUMERIC test - but what are you going to do with fields like 28:45? Ignore them? Treat them as matched? Unmatched?
And the ISNUMERIC returns 1 for 123.4 so you still have to play silly buggers even then:

SELECT * FROM tblSalaryRecordCompliance WHERE SalarySTDT ='1-Mar-2014' AND ISNUMERIC(OTHours) AND CAST(CAST(OTHours AS FLOAT) AS INT)>52

但是使用数字字段会好得多！

But you would be much, much better off using numeric fields!

SELECT * FROM tblSalaryRecordCompliance WHERE SalarySTDT ='1-Mar-2014' AND ISNUMERIC(REPLACE(OTHours , ':', '.')) AND CAST(REPLACE(OTHours , ':', '.') AS FLOAT) >52

这篇关于SQL转换问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！