表t1
字段:completeddate, createdate
数据:2012-11-19 11:30 2012-11-20 10:00:00
2012-11-23 11:30 2012-11-26 09:00:00需求:createdate为数据创建日期,completeddate为完成日期,设定工作日为星期1-5,
需要查出所有延迟录入的信息,条件是“createdate”晚于“completeddate”的下一工作日(如果completeddate是周五、周六、周日,那么下一工作日是下周一)上午9:30的人的相关信息多谢了!
字段:completeddate, createdate
数据:2012-11-19 11:30 2012-11-20 10:00:00
2012-11-23 11:30 2012-11-26 09:00:00需求:createdate为数据创建日期,completeddate为完成日期,设定工作日为星期1-5,
需要查出所有延迟录入的信息,条件是“createdate”晚于“completeddate”的下一工作日(如果completeddate是周五、周六、周日,那么下一工作日是下周一)上午9:30的人的相关信息多谢了!
IF(OBJECT_ID('TA','U') IS NOT NULL) DROP TABLE TA
CREATE TABLE TA(completeddate DATETIME,createdate DATETIME)
INSERT INTO TA
SELECT '2012-11-19 11:30','2012-11-20 10:00'
UNION ALL SELECT '2012-11-23 11:30','2012-11-26 09:00'
UNION ALL SELECT '2012-11-16 11:30','2012-11-17 09:00' SELECT createdate,completeddate,
CASE DATEPART(weekday,Ndate) WHEN 5 THEN DATEADD(D,3,A.Ndate)
WHEN 6 THEN DATEADD(D,2,A.Ndate)
WHEN 7 THEN DATEADD(D,1,A.Ndate)
ELSE Ndate END AS Ndate
FROM (
SELECT createdate,completeddate,
DATEADD(d,1,convert(varchar(10),completeddate,120))+'9:30' AS Ndate FROM TA )AS A
WHERE A.createdate< Ndate
SET DATEFIRST 1;
SELECT *,datepart(dw,completeddate),datepart(dw,createdate),
convert(varchar(12),dateadd(DAY,1-datepart(dw,completeddate)+7,completeddate),111)+' 9:30',datepart(WEEK,completeddate),datepart(WEEK,createdate)
FROM dbo.csdn1
WHERE completeddate<=createdate AND
datepart(dw,completeddate)>4
AND
( (datepart(WEEK,createdate) -datepart(WEEK,completeddate))=1
OR
(datepart(WEEK,createdate)=1 AND datepart(WEEK,completeddate)>1) --跨年
)
AND (datepart(dw,completeddate)>=1 OR datepart(dw,createdate)<=5)
AND createdate >=convert(varchar(20),dateadd(DAY,1-datepart(dw,completeddate),completeddate)+7,111)+' 9:30'SET DATEFIRST 1 我设置周一是一周的开始 老美 周末是开始
--Value First day of the week is
--1 Monday
--2 Tuesday
--3 Wednesday
--4 Thursday
--5 Friday
--6 Saturday
--7 (default, U.S. English) Sunday
from Tablename
where createdate>CONVERT(varchar(10),dateadd(day,case when datepart(dw,completeddate)=6 then 3 when datepart(dw,completeddate)=7 then 2 else 1 end,completeddate),23)+' 09:30:00'
where createdate>(convert(varchar(12) , case DATEPART(weekday,completeddate - 1)
when 5 then dateadd(dd,3,completeddate)
when 6 then dateadd(dd,2,completeddate)
else dateadd(dd,1,completeddate) end, 23 )+' 09:30:00')
(
completeddate datetime,
createdate datetime
)
insert into t1 values('2012-11-19 11:30','2012-11-20 10:00:00')
insert into t1 values('2012-11-23 11:30','2012-11-26 09:00:00')
insert into t1 values('2012-11-23 11:30','2012-11-30 09:40:00')select * from t1
where DATEPART(WK,createdate) > DATEPART(WK,completeddate)
and DATEPART(HH,createdate)>=9 AND DATEPART(MI,createdate) >30
-- Author :TravyLee(物是人非事事休,欲语泪先流!)
-- Date :2012-11-22 09:28:25
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
go
create table [test]
(
[completeddate] datetime,
[createdate] datetime
)
insert [test]
select '2012-11-19 11:30','2012-11-20 10:00:00' union all
select '2012-11-23 11:30','2012-11-26 09:00:00'
go
select
*
from
test
where
[createdate]>=case when datepart(WEEKDAY,[completeddate])=6
then convert(varchar(10),DATEADD(dd,3,[completeddate]),120)+' 09:30:00.000'
when datepart(WEEKDAY,[completeddate])=7
then convert(varchar(10),DATEADD(dd,2,[completeddate]),120)+' 09:30:00.000'
else convert(varchar(10),DATEADD(dd,1,[completeddate]),120)+' 09:30:00.000'
end/*
completeddate createdate
----------------------- -----------------------
2012-11-19 11:30:00.000 2012-11-20 10:00:00.000(1 行受影响)
*/