请问一个日期计算sql-谢谢了

请教一个日期计算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: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 行受影响)


*/

[其他解释]

select completeddate,createdate,DATEPART(weekday,completeddate - 1) from abc 
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')

查看更多 下一篇