SELECT 时间 FROM TABLE ORDER BY CASE WHEN 时间='星期五' THEN 0 WHEN 时间='星期六' THEN 1 WHEN 时间='星期日' THEN 2 WHEN 时间='星期一' THEN 3 WHEN 时间='星期二' THEN 4 WHEN 时间='星期三' THEN 5 WHEN 时间='星期四' THEN 6 END
declare @startdate datetime,@enddate datetime set @startdate='2009-08-30' set @enddate='2009-09-05'select datename(weekday,convert(varchar(10),dateadd(day,number,@startdate),120)) as [time] from master..spt_values where datediff(day,dateadd(day,number,@startdate), @enddate)>=0 and number>0 and type='p'
--直接这样 order by charindex('6712345',datepart(weekday,时间字段))
declare @startdate datetime,@enddate datetime set @startdate='2009-08-30' set @enddate='2009-09-05' ;with f as ( select datename(weekday,convert(varchar(10),dateadd(day,number,@startdate),120)) as [time],px=ROW_NUMBER()over(order by getdate()) from master..spt_values where datediff(day,dateadd(day,number,@startdate), @enddate)>=0 and number>0 and type='p' )select max(case px when 1 then time else '' end), max(case px when 2 then time else '' end), max(case px when 3 then time else '' end), max(case px when 4 then time else '' end), max(case px when 5 then time else '' end), max(case px when 6 then time else '' end) from f /*------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ 星期一 星期二 星期三 星期四 星期五 星期六(1 行受影响)*/
--MD,直接复制被坑了,反了,改下 order by charindex(datename(weekday,时间字段),'6712345')
select printTime from ticket where printTime>='2011-10-11' and printTime<'2011-10-15' order by charindex('6712345',datepart(weekday,printTime)) 15楼的朋友这样子不行呀
--看下11楼,改过了之前是复制的 select printTime from ticket where printTime>='2011-10-11' and printTime<'2011-10-15' order by charindex(datename(weekday,时间字段),'6712345')
select printTime from ticket where printTime>='2011-10-11' and printTime<'2011-10-15' order by charindex(datename(weekday,printTime),'6712345')不行哦
哦,好吧,貌似是要做万年历,这个用sql做没什么意思,程序里处理吧
给个参考:转的--创建函数(第一版)(作者:dobear_0922)create function fn_Calendar(@year int, @month int) returns nvarchar(4000) as begin declare @result nvarchar(4000), @Enter nvarchar(8) select @Enter = char(13)+char(10), @result = ' Sun Mon Tue Wed Thu Fri Sat' + @Enter --表头 declare @start datetime, @end datetime select @start = rtrim(@year)+'-'+rtrim(@month)+'-1', @end = dateadd(mm, 1, @start) set @result = @result+replicate(' ', (datepart(dw, @start)+@@datefirst+6)%7) --第一行前面的空格 while datediff(d, @start, @end)>0 begin if (datepart(dw, @start)+@@datefirst)%7 = 1 select @result = @result+@Enter --是否换行 select @result = @result+right(' '+rtrim(day(@start)), 4), @start = dateadd(d, 1, @start) end return @result endgo--测试示例 set datefirst 3 print dbo.fn_Calendar(2011, 10) set datefirst 7 --运行结果 /* Sun Mon Tue Wed Thu Fri Sat 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 */ go--创建函数(第二版)(作者:libin_ftsafe)create function f_calendar(@year int,@month int) returns @t table(日 varchar(4),一 varchar(4),二 varchar(4),三 varchar(4),四 varchar(4),五 varchar(4),六 varchar(4)) as begin declare @a table(id int identity(0,1),date datetime) insert into @a(date) select top 31 rtrim(@year)+'-'+rtrim(@month)+'-1' from sysobjects update @a set date=dateadd(dd,id,date) insert into @t select max(case datepart(dw,date) when 7 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 1 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 2 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 3 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 4 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 5 then rtrim(day(date)) else '' end), max(case datepart(dw,date) when 6 then rtrim(day(date)) else '' end) from @a where month(date)=@month group by (case datepart(dw,date) when 7 then datepart(week,date)+1 else datepart(week,date) end) return end go--测试示例set datefirst 1 select * from dbo.f_calendar(2011,10) --运行结果/*日 一 二 三 四 五 六 ---- ---- ---- ---- ---- ---- ---- 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 */ 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/maco_wang/archive/2011/03/20/6262162.aspx
SELECT 时间 FROM TABLE
ORDER BY
CASE
WHEN 时间='星期五' THEN 0
WHEN 时间='星期六' THEN 1
WHEN 时间='星期日' THEN 2
WHEN 时间='星期一' THEN 3
WHEN 时间='星期二' THEN 4
WHEN 时间='星期三' THEN 5
WHEN 时间='星期四' THEN 6
END
set @startdate='2009-08-30'
set @enddate='2009-09-05'select
datename(weekday,convert(varchar(10),dateadd(day,number,@startdate),120)) as [time]
from
master..spt_values
where
datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and number>0
and type='p'
/*time
------------------------------
星期一
星期二
星期三
星期四
星期五
星期六(6 行受影响)*/
--直接这样
order by charindex('6712345',datepart(weekday,时间字段))
set @startdate='2009-08-30'
set @enddate='2009-09-05'
;with f as
(
select
datename(weekday,convert(varchar(10),dateadd(day,number,@startdate),120)) as [time],px=ROW_NUMBER()over(order by getdate())
from
master..spt_values
where
datediff(day,dateadd(day,number,@startdate), @enddate)>=0
and number>0
and type='p'
)select
max(case px when 1 then time else '' end),
max(case px when 2 then time else '' end),
max(case px when 3 then time else '' end),
max(case px when 4 then time else '' end),
max(case px when 5 then time else '' end),
max(case px when 6 then time else '' end)
from
f
/*------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
星期一 星期二 星期三 星期四 星期五 星期六(1 行受影响)*/
--MD,直接复制被坑了,反了,改下
order by charindex(datename(weekday,时间字段),'6712345')
where printTime>='2011-10-11' and printTime<'2011-10-15' order by charindex('6712345',datepart(weekday,printTime))
15楼的朋友这样子不行呀
--看下11楼,改过了之前是复制的
select printTime from ticket
where printTime>='2011-10-11' and printTime<'2011-10-15' order by charindex(datename(weekday,时间字段),'6712345')
select printTime from ticket
where printTime>='2011-10-11' and printTime<'2011-10-15' order by charindex(datename(weekday,printTime),'6712345')不行哦
returns nvarchar(4000)
as
begin
declare @result nvarchar(4000), @Enter nvarchar(8)
select @Enter = char(13)+char(10), @result = ' Sun Mon Tue Wed Thu Fri Sat' + @Enter --表头
declare @start datetime, @end datetime
select @start = rtrim(@year)+'-'+rtrim(@month)+'-1', @end = dateadd(mm, 1, @start)
set @result = @result+replicate(' ', (datepart(dw, @start)+@@datefirst+6)%7) --第一行前面的空格
while datediff(d, @start, @end)>0
begin
if (datepart(dw, @start)+@@datefirst)%7 = 1
select @result = @result+@Enter --是否换行
select @result = @result+right(' '+rtrim(day(@start)), 4), @start = dateadd(d, 1, @start)
end
return @result
endgo--测试示例
set datefirst 3
print dbo.fn_Calendar(2011, 10)
set datefirst 7 --运行结果
/* Sun Mon Tue Wed Thu Fri Sat
1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31
*/ go--创建函数(第二版)(作者:libin_ftsafe)create function f_calendar(@year int,@month int)
returns @t table(日 varchar(4),一 varchar(4),二 varchar(4),三 varchar(4),四 varchar(4),五 varchar(4),六 varchar(4))
as
begin
declare @a table(id int identity(0,1),date datetime)
insert into @a(date)
select top 31 rtrim(@year)+'-'+rtrim(@month)+'-1' from sysobjects
update @a set date=dateadd(dd,id,date)
insert into @t
select
max(case datepart(dw,date) when 7 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 1 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 2 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 3 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 4 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 5 then rtrim(day(date)) else '' end),
max(case datepart(dw,date) when 6 then rtrim(day(date)) else '' end)
from
@a
where
month(date)=@month
group by
(case datepart(dw,date) when 7 then datepart(week,date)+1 else datepart(week,date) end)
return
end
go--测试示例set datefirst 1
select * from dbo.f_calendar(2011,10) --运行结果/*日 一 二 三 四 五 六
---- ---- ---- ---- ---- ---- ----
1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31
*/ 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/maco_wang/archive/2011/03/20/6262162.aspx