在sql中组合日期和时间 [英] combine date and time in sql
问题描述
嗨'老师'
我有一个简单的问题,在我的表(MSSQL DB)中有四个列'working_date'作为DateTime类型,'TIME_IN'为char,'ACTUAL_END_DATE'为datetime,'TIME_OUT'为char。
以下是问题:我想将它们组合起来(working_date + TIME_IN和ACTUAL_END_DATE + TIME_OUT)并使用'DATEDIFF(分钟,A,B)'计算工作时间,我尝试了很多次,但每次出错时,我的脚本如下:
SELECT
ISNULL(DATEDIFF(分钟,working_date + TIME_IN,ACTUAL_END_DATE + TIME_OUT)* 1.0 / 60, 0 。 0 ) as ' HOURS'
FROM ATTENDANCE
error消息如下:
转换日期和/或时转换失败来自字符串的时间。
我想要正确的方法,请帮忙,'老师'。
您好
如果您使用SQL 2008,则必须使用以下代码:
DECLARE @ Day As Int ,
@ Month 正如 Int ,
@ Year 正如 Int ,
@ Hour 作为 Int ,
@ Minute 作为 Int ,
@ Second 作为 Int ,
@ Result < span class =code-keyword> As DateTime
选择 @ Day =你的日子,
@ Month =你的月,
@ Year =您的年份,
@ Hour =您的小时,
@ Minute =你的分钟,
@ Second =你的第二个,
选择 @ Result = DATEADD(SECOND, @ Second ,DATEADD(MINUTE, @ Minute ,DATEADD(HOUR, @ Hour ,DATEADD(MONTH,( @ Year - 19 00 )* 12 + @ Month - 1 , @ Day - 1 ))))
另一方面,你必须使用:
DATEADD(第二,@二,DATEADD(分钟,@ Minute,DATEADD(HOUR,@ Youth,DATEADD(月,(@年 - 1900)* 12 + @Month - 1,@ Day - 1))))
而不是:
DATETIMEFROMPARTS(年(@StartDate),月(@StartDate),日(@StartDate) ),DATEPART(HOUR,@ StartTimeTime),DATEPART(MINUTE,@ StartTimeTime),DATEPART(SECOND,@ StartTimeTime),0)
我希望它对你有所帮助。
如果您有疑问,请告诉我。
正确的方法是:不要存储作为char的时间或时间。
将它们存储为DateTime,你可以直接对它们进行数学运算
Altern atively,将它们存储为整数分钟,并使用SQL DATEADD函数将working_date偏移该分钟数。
永远不要将数字信息存储为字符串 - 它总是让生活更加艰难。
您好b $ b
以明确的方式,您可以使用bellow查询而不是第一个解决方案:
声明 @ StartDate 作为 日期,
@ StartTimeChar 作为 nVarChar ( 10 ) ,
@ StartTimeTime 作为 时间( 0 ),
@ ResultDateTime As SmallDateTime
设置 @ StartDate = ' 2012-01-01'
设置 @ StartTimeChar = ' 20:30:00'
设置 @ StartTimeTime = Cast( @ StartTimeChar as 时间)
设置 @ ResultDateTime = DATETIMEFROMPARTS(年( @StartDate ),月份( @ StartDate ),Day( @ StartDate ), DATEPART(HOUR, @ StartTimeTime ),DATEPART (MINUTE, @ StartTimeTime ),DATEPART(SECOND, @ StartTimeTime ), 0 )
设置 @ ResultDateTime = DATEADD(第二,DATEPART (SECOND, @ StartTimeTime ),DATEADD(MINUTE,DATEPART(MINUTE, @ StartTimeTime ),DATEADD(HOURADD) ,DATEPART(HOUR, @ StartTimeTime ),DATEADD(MONTH,(年( @ StartDate ) - 1900 )* 12 +月( @ StartDate ) - 1 ,Day( @ StartDate ) - 1
选择 @ StartDate , @ StartTi meTime , @ ResultDateTime
我希望它对你有帮助你
Hi 'Teachers'
I have a simple question, in my table(MSSQL DB) have fours columns 'working_date' as DateTime type ,'TIME_IN' as char, 'ACTUAL_END_DATE ' as datetime and 'TIME_OUT' as char.
Here is the question: I want to combine them( working_date +TIME_INand ACTUAL_END_DATE +TIME_OUT) and use 'DATEDIFF(minute,A,B)' to calculate working hours, I tried many times ,but every time error come out, my script like below:
SELECT
ISNULL(DATEDIFF(minute,working_date + TIME_IN,ACTUAL_END_DATE + TIME_OUT)*1.0/60,0.0) as 'HOURS'
FROM ATTENDANCE
error message like this:
Conversion failed when converting date and/or time from character string.
I want the correct way, please help ,'Teachers'.
Hi
If you use from SQL 2008 you must use from bellow code:
DECLARE @Day As Int, @Month As Int, @Year As Int, @Hour As Int, @Minute As Int, @Second As Int, @Result As DateTime Select @Day = Your Day, @Month = Your Month, @Year = Your Year, @Hour = Your Hour, @Minute = Your Minute, @Second = Your Second, Select @Result = DATEADD(SECOND, @Second , DATEADD(MINUTE, @Minute , DATEADD(HOUR, @Hour , DATEADD(MONTH, (@Year - 1900) * 12 + @Month - 1 , @Day - 1))))
On the other hand you must use:
DATEADD(SECOND, @Second , DATEADD(MINUTE, @Minute , DATEADD(HOUR, @Hour , DATEADD(MONTH, (@Year - 1900) * 12 + @Month - 1 , @Day - 1))))
Instead of:
DATETIMEFROMPARTS(Year(@StartDate), Month(@StartDate), Day(@StartDate), DATEPART(HOUR,@StartTimeTime), DATEPART(MINUTE,@StartTimeTime) , DATEPART(SECOND,@StartTimeTime) , 0)
I Hope it's helpful for you.
If you have question, Please tell me.
The"correct" way to do it is: don't store the time in or time out as char.
Store them as DateTime instead, and you can do math on them directly
Alternatively, store them as an integer number of minutes, and use the SQL DATEADD function to offset the working_date by that number of minutes.
Never store numeric info as string - it always makes life harder later on.
Hi
In clear way, You can use from bellow query instead of first solution:
Declare @StartDate As Date, @StartTimeChar As nVarChar(10), @StartTimeTime As Time(0), @ResultDateTime As SmallDateTime Set @StartDate = '2012-01-01' Set @StartTimeChar = '20:30:00' Set @StartTimeTime = Cast(@StartTimeChar as Time) Set @ResultDateTime = DATETIMEFROMPARTS(Year(@StartDate), Month(@StartDate), Day(@StartDate), DATEPART(HOUR,@StartTimeTime), DATEPART(MINUTE,@StartTimeTime) , DATEPART(SECOND,@StartTimeTime) , 0) Set @ResultDateTime = DATEADD(SECOND, DATEPART(SECOND,@StartTimeTime) , DATEADD(MINUTE, DATEPART(MINUTE,@StartTimeTime) , DATEADD(HOUR, DATEPART(HOUR,@StartTimeTime) , DATEADD(MONTH, (Year(@StartDate) - 1900) * 12 + Month(@StartDate) - 1 , Day(@StartDate) - 1)))) Select @StartDate, @StartTimeTime, @ResultDateTime
I hope it's helpful for you.
这篇关于在sql中组合日期和时间的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!