有个开始时间和结束时间,在使用DATEDIFF函数计算后得到的是秒,如何能够将它转换为这样的格式:hh:mm:ss呢?
解决方案 »
- 查询
- reporting services制作柱状图问题
- 一个简单的查询问题,取两个查询结果的不同部分
- 好人请帮忙 求一sql语句,感觉有点难!
- 用了几年的SQL还不知道 N'Help_class' 中的 N''有什么好处?您知道吗?
- 谁知道这句话是什么意思?
- 如何在ComboBox控件里只显示不同的值
- 100分 数据显示不全的问题
- 百万级数据更新问题
- 我特别喜欢北京,因此从新疆某大学毕业就来这里了,可能是我来的不是时候,我去的雍和宫、东城区等地方的定期人才招聘会,没有发现有招软件开发的。我擅长数据库开发,用的是SQL Server,delphi。请教大师们我应该怎么找工作?谢谢!!
- 存储过程实现数据库数据迁移 高分奉上...
- 求救!!如何解决SQL超时连接过期问题!!谢谢~~
Select @Time1 = '2007-06-26 18:00:01',@Time2 = '2007-06-26 21:22:08'
Select Right(100 + DateDiff(hh, @Time1, @Time2), 2) + ':' + Right(100 + DateDiff(mi, @Time1, @Time2) % 60, 2) + ':' + Right(100 + DateDiff(ss, @Time1, @Time2) % 60, 2)
--Result
/*
03:22:07
*/
RETURNS nvarchar(50) AS
BEGIN
declare @h nvarchar(10)
declare @m nvarchar(10)
declare @s nvarchar(10)
select @h='00'+Convert(nvarchar(2),@a/3600),@m='00'+Convert(nvarchar(2),@a/60),@s='00'+Convert(nvarchar(2),@a%60)
return right(@h,2)+':'+right(@m,2)+':'+ right(@s,2)
ENDselect dbo.ss(122)
--------------------------------------------------
00:02:02(1 row(s) affected)
declare @TOTAL_S int ,@H INT ,@M INT,@S INT
SET @TOTAL_S=0
SET @H=0
SET @M=0
SET @S=0
set @s_datetime='2007-06-25 18:02:36'
set @e_datetime='2007-06-26 18:02:39'
select @TOTAL_S=datediff(ss,@s_datetime,@e_datetime)if @TOTAL_S>3600
BEGIN
SET @H=(@TOTAL_S-(@TOTAL_S%3600))/3600
SET @time=isnull(@time,'')+cast(@H AS VARCHAR(10))
SET @TOTAL_S=@TOTAL_S%3600
END
ELSE
SET @time=isnull(@time,'')+'00'IF @TOTAL_S>60
BEGIN
SET @M=(@TOTAL_S-(@TOTAL_S%60))/60
SET @time=isnull(@time,'')+':'+cast(@M AS VARCHAR(10))
SET @TOTAL_S=@TOTAL_S%60
END
ELSE
SET @time=isnull(@time,'')+':00'IF @TOTAL_S>0
IF @TOTAL_S>10
BEGIN
SET @S=@TOTAL_S
SET @time=isnull(@time,'')+':'+cast(@S AS VARCHAR(10))
END
ELSE
BEGIN
SET @S=@TOTAL_S
SET @time=isnull(@time,'')+':0'+cast(@S AS VARCHAR(10))
END
ELSE
SET @time=isnull(@time,'')+':00'PRINT @TIME
Select Right(100 + DateDiff(hh, @Time1, @Time2), 2)--为什么要加上'100'
Select Right(100 + DateDiff(hh, @Time1, @Time2), 2)--为什么要加上'100'
为了取得的数字都是两位的,就是如果是1 ,会取出01
--4.INSE_RECORD
CREATE PROC INSE_RECORD
@DATE DATETIME, --日期
@BC int --班次
AS
DECLARE @LL_TIME DATETIME
DECLARE @QSK_ID CHAR(17)
DECLARE @YH_NAME VARCHAR(20)
DECLARE @NUMBER CHAR(7)
DECLARE SELEMAX CURSOR FOR
SELECT MAX(LL_TIME) AS MAXTIME,QSK_ID,YH_NAME,Number
FROM GDW_LL_TJB
WHERE STOP='1' AND ZUIXINBIAOSHI='0'
GROUP BY QSK_ID,YH_NAME,Number
OPEN SELEMAX
FETCH NEXT FROM SELEMAX
INTO @LL_TIME,@QSK_ID,@YH_NAME,@NUMBER
WHILE @@FETCH_STATUS=0
BEGIN
declare @lishi int
declare @liuliang float
declare @shuiwei float
select @liuliang=liuliang,@shuiwei=shuiwei
from gdw_ll_tjb
where qsk_id=@QSK_ID and ll_time=@LL_TIME and yh_name=@YH_NAME and number=@NUMBER
if @BC=0 --早班
BEGIN
--插入并更新7:59:59
INSERT GDW_LL_TJB(QSK_ID,LL_TIME,YH_NAME,Number,LIULIANG,LR_TIME,STOP,ZUIXINBIAOSHI,SHUIWEI)
VALUES (@QSK_ID,@DATE+'07:59:59',@YH_NAME,@NUMBER,0,CONVERT(VARCHAR(11),@DATE,120),1,0,@shuiwei)
select @lishi=DATEDIFF(second,@LL_TIME,@DATE+' 08:00:00')
UPDATE GDW_LL_TJB
SET LIULIANG=@liuliang,LiShi=@lishi,shuiliang=@lishi*@liuliang
WHERE qsk_id=@QSK_ID and ll_time=@DATE+'07:59:59' and yh_name=@YH_NAME and number=@NUMBER
--插入8:00:00
INSERT GDW_LL_TJB(QSK_ID,LL_TIME,YH_NAME,Number,LIULIANG,LR_TIME,STOP,ZUIXINBIAOSHI,SHUIWEI)
VALUES (@QSK_ID,@DATE+'08:00:00',@YH_NAME,@NUMBER,@liuliang,CONVERT(VARCHAR(11),@DATE,120),1,0,@shuiwei)
END
IF @BC=1 --中班
BEGIN
--插入并更新15:59:59
INSERT GDW_LL_TJB(QSK_ID,LL_TIME,YH_NAME,Number,LIULIANG,LR_TIME,STOP,ZUIXINBIAOSHI,SHUIWEI)
VALUES (@QSK_ID,@DATE+'15:59:59',@YH_NAME,@NUMBER,0,CONVERT(VARCHAR(11),@DATE,120),1,0,@shuiwei)
select @lishi=DATEDIFF(second,@LL_TIME,@DATE+' 16:00:00')
UPDATE GDW_LL_TJB
SET LIULIANG=@liuliang,LiShi=@lishi,shuiliang=@lishi*@liuliang
WHERE qsk_id=@QSK_ID and ll_time=@DATE+'15:59:59' and yh_name=@YH_NAME and number=@NUMBER
--插入8:00:00
INSERT GDW_LL_TJB(QSK_ID,LL_TIME,YH_NAME,Number,LIULIANG,LR_TIME,STOP,ZUIXINBIAOSHI,SHUIWEI)
VALUES (@QSK_ID,@DATE+'16:00:00',@YH_NAME,@NUMBER,@liuliang,CONVERT(VARCHAR(11),@DATE,120),1,0,@shuiwei)
END
IF @BC=2 --午班
BEGIN
--插入并更新7:59:59
INSERT GDW_LL_TJB(QSK_ID,LL_TIME,YH_NAME,Number,LIULIANG,LR_TIME,STOP,ZUIXINBIAOSHI,SHUIWEI)
VALUES (@QSK_ID,@DATE+'23:59:59',@YH_NAME,@NUMBER,0,CONVERT(VARCHAR(11),@DATE,120),1,0,@shuiwei)
select @lishi=DATEDIFF(second,@LL_TIME,DATEADD(DAY,1,@DATE)+' 0:00:00')
UPDATE GDW_LL_TJB
SET LIULIANG=@liuliang,LiShi=@lishi,shuiliang=@lishi*@liuliang
WHERE qsk_id=@QSK_ID and ll_time=@DATE+'23:59:59' and yh_name=@YH_NAME and number=@NUMBER
--插入8:00:00
INSERT GDW_LL_TJB(QSK_ID,LL_TIME,YH_NAME,Number,LIULIANG,LR_TIME,STOP,ZUIXINBIAOSHI,SHUIWEI)
VALUES (@QSK_ID,DATEADD(DAY,1,@DATE)+' 0:00:00',@YH_NAME,@NUMBER,@liuliang,CONVERT(VARCHAR(11),@DATE,120),1,0,@shuiwei)
END
FETCH NEXT FROM SELEMAX
INTO @LL_TIME,@QSK_ID,@YH_NAME,@NUMBER
END
CLOSE SELEMAX
DEALLOCATE SELEMAX
GO
沒看懂。想问一下paoluo您,第三个Right(100 + DateDiff(ss, @Time1, @Time2) % 60, 2)是不是应该%3600呀!
-----------
應該是60,樓主再想想看。
如果不清楚,QQ:785277368
select @lishi=DATEDIFF(second,@LL_TIME,@DATE+' 08:00:00')改為Select @lishi = Right(100 + DateDiff(hh, @LL_TIME, @DATE+' 08:00:00'), 2) + ':' + Right(100 + DateDiff(mi, @LL_TIME, @DATE+' 08:00:00') % 60, 2) + ':' + Right(100 + DateDiff(ss, @LL_TIME, @DATE+' 08:00:00') % 60, 2) 下面的做類似修改
另外,GDW_LL_TJB中的LiShi必須是字符型。