英文:
SQL Server : converting TIME to a number
问题
我需要将两列的每一行相乘，我们称它们为Cal1和Cal2。Cal1声明为FLOAT，Cal2声明为TIME。
我明白你不能将TIME乘以FLOAT，这很合理。所以我需要将时间（例如04:30:00）转换为可以计算的数字（例如4.50）。
举个例子，我的当前列：
| Cal1 | Cal2 |
|---|---|
| ... | 04:00:00 |
| ... | 05:30:00 |
| ... | 03:30:00 |
我需要它看起来像这样：
| Cal1 | Cal2 |
|---|---|
| ... | 4,00 |
| ... | 5,50 |
| ... | 3,50 |
这样我就可以进行计算。
我能想到的最好办法是尝试：
SELECT CATS(CAST(Cal2 AS datetime) AS float) * 24
输出显示给我正确的数字，但是如何用这些数字替换我的当前Cal2以进行计算呢？
非常感谢你！
英文:
I need to multiply each row of two columns, let's call them Cal1 and Cal2. Cal1 is declared as a FLOAT, Cal2 as a TIME.
I came to understand that you can not multiply a TIME with a FLOAT, which makes sense. So I'll have to transform the time (example 04:30:00) to a number I can calculate with (example 4.50).
For example, my current columns:
| Cal1 | Cal2 |
|---|---|
| ... | 04:00:00 |
| ... | 05:30:00 |
| ... | 03:30:00 |
I need it to look like this:
| Cal1 | Cal2 |
|---|---|
| ... | 4,00 |
| ... | 5,50 |
| ... | 3,50 |
So that I can calculate with it.
Best thing I could think of was to try:
SELECT CATS(CAST(Cal2 AS datetime) AS float) * 24
The output is showing me the correct numbers, but how do I replace my current Cal2 with these numbers to calculate with it?
Thank you so much in advance!
答案1
得分: 1
这看起来你应该添加一个计算列来利用，类似这样：
alter table YourTable
add DecimalTime as
Convert(Decimal(4, 2), DatePart(hour, cal2) + DatePart(minute, cal2) / 60.0 + DatePart(second, cal2) / 3600.0);
然后你可以简单地使用列 DecimalTime。
另外要注意，使用 float 数据类型是不常见的，它是一种不精确的数据类型，可能你应该使用 Decimal/Numeric。
英文:
This looks like you should add a computed column to make use of, something like
alter table YourTable
add DecimalTime as
Convert(Decimal(4, 2), DatePart(hour, cal2) + DatePart(minute, cal2) / 60.0 + DatePart(second, cal2) / 3600.0);
Then you can simply use the column DecimalTime.
Also note it's unusual to use the float data type, it's an imprecise data type and probably you should be using Decimal/Numeric.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论