如何在sql中划分Varchar(datetime)值? [英] How to Divide Varchar(datetime) value in sql?
问题描述
如何在sql中划分这个Varchar值
Sql server 2008运行成功。
代码在Sql:
DECLARE @TotalShiftHours varchar ( 50 )
DECLARE @TotalWorkingHours varchar ( 50 )
声明 @ HalfDayCalculate varchar ( 50 )
声明 @ DivdeWorkingHours int
select @TotalShiftHours = convert( varchar ,CAST(ShiftEnd AS DATETIME ) - CAST(ShiftStart AS DATETIME ), 8 ) FROM Accounts_DailyAttendance 其中 UserID = 1012 和 DATENAME(MONTH,ProcessDate)= ' 九月' 和 DATEPART(年,ProcessDate)= 2014
- - 每日总工作时间----
set @ DivdeWorkingHours = 2
选择 @TotalWorkingHours = convert( varchar ,CAST(OutPunch_Time) AS DATETIME ) - CAST(Punch1_Time AS DATETIME ), 8 ) FROM Accounts_DailyAttendance 其中 UserID = 1012 和 DATENAME(MONTH,ProcessDate)= ' 九月 和 DATEPART(年份,ProcessDate)= 2014
- - HalfDay计算-----
set @ HalfDayCalculate = convert ( TIME ( 0 ),dateadd(second,datediff(second, 0 , @TotalShiftHours )/ @ DivdeWorkingHours , 0 ))
选择 @HalfDayCalculate
但Sql server 2005半天计算未运行,如何半天计算在sql server 2005中。
@TotalShiftHours = ' 11:30:00'
set @ DivdeWorkingHours = 2
set @ HalfDayCalculate = convert (< span class =code-keyword> TIME ( 0 ),dateadd(second,datediff(second, 0 , @TotalShiftHours )/ @DivdeWorkingHours , 0 ))
如何查询运行sql server 2005以上获取结果是@HalfDayCalculate = '05:45: 00'
谢谢
How to divide This Varchar value in sql 这篇关于如何在sql中划分Varchar(datetime)值?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
设置@HalfDayCalculate =
convert(varchar,dateadd(second,datediff(second,0,@ CompleteShiftHours)/ @DivdeWorkingHours,0),108)
Sql server 2008 running succeessfully.
Code in Sql:
DECLARE @TotalShiftHours varchar(50)
DECLARE @TotalWorkingHours varchar(50)
Declare @HalfDayCalculate varchar(50)
Declare @DivdeWorkingHours int
select @TotalShiftHours=convert(varchar,CAST(ShiftEnd AS DATETIME)-CAST(ShiftStart AS DATETIME), 8) FROM Accounts_DailyAttendance where UserID=1012 and DATENAME(MONTH,ProcessDate)='September' and DATEPART(YEAR,ProcessDate)=2014
----Total Working Hours Perday----
set @DivdeWorkingHours=2
select @TotalWorkingHours=convert(varchar,CAST(OutPunch_Time AS DATETIME)-CAST(Punch1_Time AS DATETIME), 8) FROM Accounts_DailyAttendance where UserID=1012 and DATENAME(MONTH,ProcessDate)='September' and DATEPART(YEAR,ProcessDate)=2014
---HalfDay Calculate-----
set @HalfDayCalculate = convert(TIME(0), dateadd(second, datediff(second, 0, @TotalShiftHours) / @DivdeWorkingHours, 0))
select @HalfDayCalculate
But Sql server 2005 Half Day Calculate Not running, How to Half day Calculate in sql server 2005.
@TotalShiftHours='11:30:00'
set @DivdeWorkingHours=2
set @HalfDayCalculate = convert(TIME(0), dateadd(second, datediff(second, 0, @TotalShiftHours) / @DivdeWorkingHours, 0))
How to above query running sql server 2005 Get result is @HalfDayCalculate ='05:45:00'
Thankstry the below code line :-
set @HalfDayCalculate =
convert(varchar,dateadd(second, datediff(second, 0, @TotalShiftHours) / @DivdeWorkingHours, 0),108)