SQL自动打卡

[u010535898]

INSERT INTO [Attend20].[dbo].[AttdCardRec]([UserId], [CardId],
[CardDate], [CardTime], [CardInOut], [CardCollectDate], [CardEqu], [CardSignFlag],
[CardReason], [EquType], [OperID], [OperDate], [CardFlag])
VALUES('UT000812','00000005',CONVERT(varchar(10),getdate(),120),'07:45','',
CONVERT(varchar(10),getdate(),120),'001','0','',
'','',NULL,'019')请问怎样把07:45的值改成07:30-08:00的随机值

[lzw_0736]

WITH a1 (tt) AS 
(
SELECT '07:45'
)
,a2 AS
(
SELECT CAST('2013/01/01 '+tt AS DATETIME) tt
FROM a1
)
SELECT RIGHT(convert(CHAR(16),DATEADD(MINUTE,-15,tt),20),5)+'-'+RIGHT(convert(CHAR(16),DATEADD(MINUTE,15,tt),20),5)
FROM a2

[Shawn]

INSERT  INTO [Attend20].[dbo].[AttdCardRec]
        ( [UserId] ,
          [CardId] ,
          [CardDate] ,
          [CardTime] ,
          [CardInOut] ,
          [CardCollectDate] ,
          [CardEqu] ,
          [CardSignFlag] ,
          [CardReason] ,
          [EquType] ,
          [OperID] ,
          [OperDate] ,
          [CardFlag]
        )
VALUES  ( 'UT000812' ,
          '00000005' ,
          CONVERT(VARCHAR(10), GETDATE(), 120) ,
          (CASE WHEN ABS(CHECKSUM(NEWID())%31) = 30 THEN '08:00' ELSE '07:' + RIGHT('00'+LTRIM(ABS(CHECKSUM(NEWID())%31)+30), 2) END) ,
          '' ,
          CONVERT(VARCHAR(10), GETDATE(), 120) ,
          '001' ,
          '0' ,
          '' ,
          '' ,
          '' ,
          NULL ,
          '019'
        )

[yujh518]

建议把RIGHT('00'+LTRIM(ABS(CHECKSUM(NEWID())%31)+30), 2)修改为RIGHT('00'+LTRIM(ABS(CHECKSUM(NEWID())%31)+29), 2) 否则会出来07：60的情况。

[---涛声依旧---]

呵呵，这样楼主以后可以不用打卡了

[---涛声依旧---]

--确实会出现07:60的情况，修正为：+29
select (CASE WHEN ABS(CHECKSUM(NEWID())%31) = 30 THEN '08:00' ELSE '07:' + RIGHT('00'+LTRIM(ABS(CHECKSUM(NEWID())%31)+29), 2) END)

[lzw_0736]

INSERT INTO [Attend20].[dbo].[AttdCardRec]([UserId], [CardId], 
[CardDate], [CardTime], [CardInOut], [CardCollectDate], [CardEqu], [CardSignFlag], 
[CardReason], [EquType], [OperID], [OperDate], [CardFlag])
VALUES('UT000812','00000005',CONVERT(varchar(10),getdate(),120),
RIGHT(CONVERT(CHAR(16),DATEADD(MINUTE,CAST(RAND()*100 AS INT)%31,'2013-01-01 07:30'),120),5),--'07:45',
'', CONVERT(varchar(10),getdate(),120),'001','0','',
'','',NULL,'019')

[loveshrimp423]

我是做个触发器，采集的时候也是自动写入随机时间

[tcmakebest]

随机时间，这明显是作弊。 这个期间共有30分钟，先生成0-30的随机数，然后这样： select RIGHT(CONVERT(varchar(16), DATEADD(MI,RAND()*31, CONVERT(datetime,'07:30')),120),6)