转换为分钟 [英] conversion to minute
问题描述
WITH SampleData(员工,第1天,第2天,第3天,第31天)
AS
(
SELECT'MGR','9H' ,'30M',NULL,'10M'
UNION ALL
SELECT'Sivaji','0M','1H','30M',' - 4H45M'
UNION ALL
SELECT'Rajini','20M',' - 1H',NULL,NULL
)
从SampleData中选择*
这是我的表数据我需要输出几分钟
输出必须是
如果9H - 我需要到540米
如果-4h45m - 我需要到-285米
这类结果的查询是什么
当你发布的时候一小时前同样的问题:将小时数转换为分钟数 [ ^ ]我告诉过你这是一个糟糕的主意。
一小时后它仍然是一个糟糕的主意。
当一切都是字符串时,插入数据可能很简单,但是用它做任何事情都变得非常复杂。将其存储在适当的数据类型中!
查看对您之前问题的回复 - 将小时数转换为分钟数 [ ^ ],以及此帖子的解决方案1.
如果你坚持使用然后你需要执行以下表格:
- 创建功能 [ ^ ]fnGetMinutes
将varchar
作为输入并返回int
- 使用 CHARINDEX [ ^ ]获取'h'和/或'm'的位置的函数
- 使用 SUBSTRING [ ^ ]获取小时和分钟值的函数
- 您可能想要使用REPLACE [ ^ ]在'm'上运行以保持简单。
- 然后你需要 CAST [ ^ ] varchar结果为整数
- 将小时数乘以60,添加任何分钟并返回值
请记住,以满足没有'h'的值,a nd /或没有'm',输入可能在哪里NULL
完成后你可以然后使用类似于以下的查询(回答此类结果的查询是什么)的问题。SELECT Employee,
[dbo] .fnGetMinutes(day1),
[dbo] .fnGetMinutes(day2),
[dbo] .fnGetMinutes(day3),
[dbo] .fnGetMinutes(day31 )
来自SampleData
或者,如果你有更好的表格设计,它就更简单了,例如WITH BetterWay(员工,DayNo,Hrs,Mins)
AS
(
SELECT ' MGR', 1 , 9 , 0
联盟 全部
SELECT ' MGR', 2 , 0 , 30
- ETC包含其余数据
)
SELECT 员工,DayNo,( 60 * ISNULL(小时, 0 ))+ ISNULL(分钟,< span class =code-digit> 0 )
来自 BetterWay
它还可以处理少于31天的月份。
WITH SampleData (Employee, Day1, Day2, Day3, Day31)
AS
(
SELECT 'MGR','9H','30M',NULL,'10M'
UNION ALL
SELECT 'Sivaji','0M','1H','30M','-4H45M'
UNION ALL
SELECT 'Rajini','20M','-1H',NULL, NULL
)
select * from SampleData
this is my table data i need an output in minutes
output must be
if 9H -- I need that into 540 m
if -4h45m -- I need that into -285 m
what was the query for this type of results
When you posted this same question an hour ago: to convert hours to minutes[^] I told you it was a poor idea.
One hour later it is still a poor idea.
It may be simple for you to insert the data when everything is a string, but it gets horribly complicated to do anything with it. Store it in appropriate data types!
See the response to your earlier question - to convert hours to minutes[^], and Solution 1 to this post.
If you insist on using the current table schema then you will need to do the following:
- Create a function[^]fnGetMinutes
that takes avarchar
as input and returns anint
- Use the CHARINDEX[^] function to get the position of the 'h' and/or 'm'
- Use the SUBSTRING[^] function to get the values of hours and minutes
- You may want to use the REPLACE[^] function on the 'm' to keep it simple.
- You then need to CAST[^] the varchar results to integers
- Multiply the hours by 60, add any minutes and return the value
Remember to cater for values that have no 'h', and/or no 'm' and where the input may beNULL
Once you have done that you can then use a query similar to the following (to answer the question "what was the query for this type of results")SELECT Employee, [dbo].fnGetMinutes(day1), [dbo].fnGetMinutes(day2), [dbo].fnGetMinutes(day3), [dbo].fnGetMinutes(day31) FROM SampleData
Alternatively if you have a better table design it is far far simpler e.g.WITH BetterWay (Employee, DayNo, Hrs, Mins) AS ( SELECT 'MGR', 1, 9, 0 Union all SELECT 'MGR' ,2, 0, 30 -- ETC with the rest of your data ) SELECT Employee, DayNo, (60 * ISNULL(Hrs,0)) + ISNULL(Mins,0) from BetterWay
It also handles months that have less than 31 days far better.
这篇关于转换为分钟的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!