日期:2014-05-17  浏览次数:20545 次

请教一个日期计算sql----------多谢了!
表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的人的相关信息

多谢了!
------最佳解决方案--------------------

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

------其他解决方案--------------------
select *
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'
------其他解决方案--------------------
null
------其他解决方案--------------------

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


------其他解决方案--------------------
create table t1
(
   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