这样的sql如何写：要取出某月的所有天数及相应的天数是星期几

Question

查某一天是星期几set datefirst 1
select datepart(weekday,getdate())如果有现成的时间纬度表，就可以用时间纬度表和datepart(weekday,日期)来得到你要的结果了

Answer 1

declare @date smalldatetime,@startdate smalldatetime
declare @tb table(date smalldatetime)set @date='2008-10-24' 
set @startdate=cast(left(convert(varchar(10),@date,120),8)+'01' as smalldatetime)while datediff(mm,@startdate,@date)=0
begin
   insert @tb select @startdate
   set @startdate=dateadd(day,1,@startdate)
endset DATEFIRST 1
select *,datename(dw,date) as weeks from @tb 
/*
date                                                   weeks                          
------------------------------------------------------ ------------------------------ 
2008-10-01 00:00:00                                    Wednesday
2008-10-02 00:00:00                                    Thursday
2008-10-03 00:00:00                                    Friday
2008-10-04 00:00:00                                    Saturday
2008-10-05 00:00:00                                    Sunday
2008-10-06 00:00:00                                    Monday
2008-10-07 00:00:00                                    Tuesday
2008-10-08 00:00:00                                    Wednesday
2008-10-09 00:00:00                                    Thursday
2008-10-10 00:00:00                                    Friday
2008-10-11 00:00:00                                    Saturday
2008-10-12 00:00:00                                    Sunday
2008-10-13 00:00:00                                    Monday
2008-10-14 00:00:00                                    Tuesday
2008-10-15 00:00:00                                    Wednesday
2008-10-16 00:00:00                                    Thursday
2008-10-17 00:00:00                                    Friday
2008-10-18 00:00:00                                    Saturday
2008-10-19 00:00:00                                    Sunday
2008-10-20 00:00:00                                    Monday
2008-10-21 00:00:00                                    Tuesday
2008-10-22 00:00:00                                    Wednesday
2008-10-23 00:00:00                                    Thursday
2008-10-24 00:00:00                                    Friday
2008-10-25 00:00:00                                    Saturday
2008-10-26 00:00:00                                    Sunday
2008-10-27 00:00:00                                    Monday
2008-10-28 00:00:00                                    Tuesday
2008-10-29 00:00:00                                    Wednesday
2008-10-30 00:00:00                                    Thursday
2008-10-31 00:00:00                                    Friday
*/

Answer 2

declare @t table (ym char(7))
insert into @t
select '2008-06' 
union all
select '2008-07'
UNION ALL 
select '2008-08'
UNION ALL 
select '2008-09'
UNION ALL 
select '2008-10'
DECLARE @a int
SELECT @a=@@DATEFIRST
SET DATEFIRST 7
DECLARE @d table(id int identity(0,1),a int)
INSERT @d SELECT TOP 31 0 from syscolumns
SELECT ym 月,dateadd(day,id,dat) 日期,
datename(weekday,dateadd(day,id,dat)) 星期
FROM @d aa ,
(SELECT ym,cast(ym+'-01' AS smalldatetime) dat,datediff(day,cast(ym+'-01' AS smalldatetime),dateadd(month,1,cast(ym+'-01' AS smalldatetime))) ee FROM @t) bb
WHERE id<eeset datefirst @a

Answer 3

DECLARE @d DATETIME
SET @d='2008-4-1';with fc as
(
select 0 id
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
)
SELECT d,'星期' + ISNULL(NULLIF(RTRIM(DATEPART(dw,d)-1),'0'),'天') FROM
(
SELECT CONVERT(VARCHAR(8),@d,120) + RTRIM(a.id) + RTRIM(b.id) d
FROM fc a
INNER JOIN fc b
ON ISDATE(CONVERT(VARCHAR(8),@d,120) + RTRIM(a.id) + RTRIM(b.id))=1
) a
/*
2008-04-01 星期2
2008-04-02 星期3
2008-04-03 星期4
2008-04-04 星期5
2008-04-05 星期6
2008-04-06 星期天
2008-04-07 星期1
2008-04-08 星期2
2008-04-09 星期3
2008-04-10 星期4
2008-04-11 星期5
2008-04-12 星期6
2008-04-13 星期天
2008-04-14 星期1
2008-04-15 星期2
2008-04-16 星期3
2008-04-17 星期4
2008-04-18 星期5
2008-04-19 星期6
2008-04-20 星期天
2008-04-21 星期1
2008-04-22 星期2
2008-04-23 星期3
2008-04-24 星期4
2008-04-25 星期5
2008-04-26 星期6
2008-04-27 星期天
2008-04-28 星期1
2008-04-29 星期2
2008-04-30 星期3
*/

Answer 4

自己照着写了一个declare  @datex  datetime ,@dstart datetime,@dend datetime
declare @tb table(date smalldatetime)
set  @datex  =  '2008-10-9' 
set @dstart= cast(cast(datepart(year,@datex)as varchar(4))+'-'+cast(datepart(month,@datex) as varchar(2))+'-'+'01' as datetime)  --得到这个月的起始日期set @dend = dateadd(second,-3,
cast(cast(datepart(year,@datex)as varchar(4))+'-'+cast(datepart(month,@datex)+1 as varchar(2))+'-'+'01' as datetime)) --得到这个月的终止日期
--print convert(varchar(10),@dstart,120)
--print convert(varchar(10),@dend,120)
while @dstart<=@dend
begininsert @tb select @dstart
   set @dstart=dateadd(day,1,@dstart)
end
set DATEFIRST 1
select *,datename(dw,date) as weeks from @tb