exec sp_W 'startime','endtime'
列出一段时间的数据,但是周六日为空。我想把周六日的时间显示出来,这个结果为空, 代码是
------------------------------
语句
select w.mydate,s.name,dept from worka w,staff s ,Depd
where w.Name = s.name
and s.dept = d.dept
and (w.workdate between @begindate and @enddate )
列出一段时间的数据,但是周六日为空。我想把周六日的时间显示出来,这个结果为空, 代码是
------------------------------
语句
select w.mydate,s.name,dept from worka w,staff s ,Depd
where w.Name = s.name
and s.dept = d.dept
and (w.workdate between @begindate and @enddate )
where w.Name = s.name
and s.dept = d.dept
and (w.workdate between @begindate and @enddate )
union allselect * from
你的日历表
where mydatetime between @begindate and @enddate
and DATEPART(dw, mydatetime ) in (6,7)
自己做一个日历表。
declare @edate datetime
set @sdate = '2009-8-30'
set @edate = '2009-9-5'
select
dateadd(dd,num,@sdate)
from
(select isnull((select count(1) from sysobjects where id <t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate) <=@edate /*
------------------------------------------------------
2009-08-30 00:00:00.000
2009-08-31 00:00:00.000
2009-09-01 00:00:00.000
2009-09-02 00:00:00.000
2009-09-03 00:00:00.000
2009-09-04 00:00:00.000
2009-09-05 00:00:00.000 (所影响的行数为 7 行)
*/--功能:找出在2个日期之间的日期
--startdate:2009年9月15日 endDate:2009年10月3日 declare @startdate datetime,@enddate datetime
set @startdate='2009-08-30'
set @enddate='2009-09-05'select convert(varchar(10),dateadd(day,number,@startdate),120)
from
master..spt_values
where
datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and number>0
and type='p'/*----------
2009-08-31
2009-09-01
2009-09-02
2009-09-03
2009-09-04
2009-09-05(6 行受影响)/*本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/fredrickhu/archive/2009/09/24/4587448.aspx
set @begindate='2011-03-05'
set @enddate='2011-03-15'select dateadd(dd,number,@begindate) as date
from master..spt_values
where type='p' and number<datediff(day,@begindate,@enddate)+1
/*
date
-----------------------
2011-03-05 00:00:00.000
2011-03-06 00:00:00.000
2011-03-07 00:00:00.000
2011-03-08 00:00:00.000
2011-03-09 00:00:00.000
2011-03-10 00:00:00.000
2011-03-11 00:00:00.000
2011-03-12 00:00:00.000
2011-03-13 00:00:00.000
2011-03-14 00:00:00.000
2011-03-15 00:00:00.000 */--可修改为select * from
(select dateadd(dd,number,@begindate) as date
from master..spt_values
where type='p' and number<datediff(day,@begindate,@enddate)+1) a
left join (
select w.mydate,s.name,dept from worka w,staff s ,Depd
where w.Name = s.name
and s.dept = d.dept
and (w.workdate between @begindate and @enddate )) b on a.date=b.mydate
(select dateadd(dd,number,@begindate) as date
from master..spt_values
where type='p' and number<datediff(day,@begindate,@enddate)+1) a
left join (
select w.mydate,s.name,dept from worka w,staff s ,Depd
where w.Name = s.name
and s.dept = d.dept
and (w.workdate between @begindate and @enddate )) b on a.date=b.mydate