27,579
社区成员
发帖
与我相关
我的任务
分享
select case when DateDiff(dd,createon,GetDate())=1 then '昨天'
when DateDiff(dd,createon,GetDate())=0 then '今天'
end
from T_Message
DECLARE @TB TABLE([DT] DATETIME)
INSERT @TB
SELECT '2010-4-15 16:27:32' UNION ALL
SELECT '2010-4-14 16:27:32' UNION ALL
SELECT '2010-4-13 16:27:32' UNION ALL
SELECT '2010-4-12 16:27:32' UNION ALL
SELECT '2010-4-11 16:27:32' UNION ALL
SELECT '2010-4-10 16:27:32' UNION ALL
SELECT '2010-4-9 16:27:32' UNION ALL
SELECT '2010-4-8 16:27:32' UNION ALL
SELECT '2010-4-7 16:27:32' UNION ALL
SELECT '2010-3-15 16:27:32' UNION ALL
SELECT '2010-3-14 16:27:32' UNION ALL
SELECT '2010-3-13 16:27:32' UNION ALL
SELECT '2010-3-12 16:27:32' UNION ALL
SELECT '2010-4-11 16:27:32'
SET DATEFIRST 1
SELECT case when DateDiff(dd,DT,GetDate())=1 then '昨天'
when DateDiff(dd,DT,GetDate())=0 then '今天'
WHEN DATEDIFF(DAY, DATEADD(DAY, 0, W1), DT)=0 THEN N'周'+RTRIM(DATEPART(DW,DT))
WHEN DATEDIFF(DAY, DATEADD(DAY, 1, W1), DT)=0 THEN N'周'+RTRIM(DATEPART(DW,DT))
WHEN DATEDIFF(DAY, DATEADD(DAY, 2, W1), DT)=0 THEN N'周'+RTRIM(DATEPART(DW,DT))
WHEN DATEDIFF(DAY, DATEADD(DAY, 3, W1), DT)=0 THEN N'周'+RTRIM(DATEPART(DW,DT))
WHEN DATEDIFF(DAY, DATEADD(DAY, 4, W1), DT)=0 THEN N'周'+RTRIM(DATEPART(DW,DT))
WHEN DATEDIFF(DAY, DATEADD(DAY, 5, W1), DT)=0 THEN N'周'+RTRIM(DATEPART(DW,DT))
WHEN DATEDIFF(DAY, DATEADD(DAY, 6, W1), DT)=0 THEN N'周'+RTRIM(DATEPART(DW,DT))
ELSE N'一周前'
END AS DW
FROM (
SELECT *, DATEADD(DAY, -6, GETDATE()) AS W1
FROM @TB
WHERE DATEDIFF(DAY, DT, GETDATE())>=0
) T
/*
DW
-------------
今天
昨天
周2
周1
周7
周6
周5
一周前
一周前
一周前
一周前
一周前
一周前
周7
(14 row(s) affected)
*/
create table T_Message (createon datetime)
insert into T_Message values('2010-4-15 16:27:32')
insert into T_Message values('2010-4-14 16:27:32')
insert into T_Message values('2010-4-13 16:27:32')
insert into T_Message values('2010-4-12 16:27:32')
insert into T_Message values('2010-4-11 16:27:32')
insert into T_Message values('2010-4-10 16:27:32')
insert into T_Message values('2010-4-9 16:27:32')
insert into T_Message values('2010-4-8 16:27:32')
insert into T_Message values('2010-4-7 16:27:32')
insert into T_Message values('2010-3-15 16:27:32')
insert into T_Message values('2010-3-14 16:27:32')
insert into T_Message values('2010-3-13 16:27:32')
insert into T_Message values('2010-3-12 16:27:32')
insert into T_Message values('2010-4-11 16:27:32')
go
declare @sql varchar(8000)
select @sql = 'select '+char(13)+char(10)+
(select top 1 '(select top 1 createon from T_Message where DateDiff(dd,createon,GetDate())=0 ) as '+'今天' from T_Message where DateDiff(dd,createon,GetDate())=0)+','+char(13)+char(10)+
(select top 1 '(select top 1 createon from T_Message where DateDiff(dd,createon,GetDate())=1 ) as '+'昨天' from T_Message where DateDiff(dd,createon,GetDate())=1)+','+char(13)+char(10)+
(select top 1 '(select top 1 createon from T_Message where DateDiff(dd,createon,GetDate())=2 ) as '+( datename(w,createon) ) from T_Message where DateDiff(dd,createon,GetDate())=2)+','+char(13)+char(10)+
(select top 1 '(select top 1 createon from T_Message where DateDiff(dd,createon,GetDate())=3 ) as '+( datename(w,createon) ) from T_Message where DateDiff(dd,createon,GetDate())=3)+','+char(13)+char(10)+
(select top 1 '(select top 1 createon from T_Message where DateDiff(dd,createon,GetDate())=4 ) as '+( datename(w,createon) ) from T_Message where DateDiff(dd,createon,GetDate())=4)+','+char(13)+char(10)+
(select top 1 '(select top 1 createon from T_Message where DateDiff(dd,createon,GetDate())=5 ) as '+( datename(w,createon) ) from T_Message where DateDiff(dd,createon,GetDate())=5)+','+char(13)+char(10)+
(select top 1 '(select top 1 createon from T_Message where DateDiff(dd,createon,GetDate())=6 ) as '+( datename(w,createon) ) from T_Message where DateDiff(dd,createon,GetDate())=6)+','+char(13)+char(10)+
(select top 1 '(select top 1 createon from T_Message where DateDiff(dd,createon,GetDate())>6 ) as '+'一周前' from T_Message where DateDiff(dd,createon,GetDate())>6)
exec( @sql)
今天 昨天 星期二 星期一 星期日 星期六 星期五 一周前
------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
2010-04-15 16:27:32.000 2010-04-14 16:27:32.000 2010-04-13 16:27:32.000 2010-04-12 16:27:32.000 2010-04-11 16:27:32.000 2010-04-10 16:27:32.000 2010-04-09 16:27:32.000 2010-04-08 16:27:32.000
select '今天' d1,'昨天' d2,replace(datename(w,getdate()-2),'星期','周') d3,
replace(datename(w,getdate()-3),'星期','周') d3,replace(datename(w,getdate()-4),'星期','周') d3,
replace(datename(w,getdate()-5),'星期','周') d3,replace(datename(w,getdate()-6),'星期','周') d3,
d8= '一周前'
/*
d1 d2 d3 d3 d3 d3 d3 d8
今天 昨天 周二 周一 周日 周六 周五 一周前
*/
这样不就可以了吗create table tb(createon datetime)
insert into tb values('2010-4-15 16:27:32')
insert into tb values('2010-4-14 16:27:32')
insert into tb values('2010-4-13 16:27:32')
insert into tb values('2010-4-12 16:27:32')
insert into tb values('2010-4-11 16:27:32')
insert into tb values('2010-4-10 16:27:32')
insert into tb values('2010-4-9 16:27:32')
insert into tb values('2010-4-8 16:27:32')
insert into tb values('2010-4-7 16:27:32')
insert into tb values('2010-3-15 16:27:32')
insert into tb values('2010-3-14 16:27:32')
insert into tb values('2010-3-13 16:27:32')
insert into tb values('2010-3-12 16:27:32')
insert into tb values('2010-4-11 16:27:32')
go
select
t1 = '今天' ,
t2 = '昨天' ,
t3 = datename(weekday,(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 2)),
t4 = datename(weekday,(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 3)),
t5 = datename(weekday,(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 4)),
t6 = datename(weekday,(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 5)),
t7 = datename(weekday,(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 6)),
t8 = '一周前'
union all
select
t1 = convert(varchar(20),(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 0),120),
t2 = convert(varchar(20),(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 1),120),
t3 = convert(varchar(20),(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 2),120),
t4 = convert(varchar(20),(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 3),120),
t5 = convert(varchar(20),(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 4),120),
t6 = convert(varchar(20),(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 5),120),
t7 = convert(varchar(20),(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 6),120),
t8 = convert(varchar(20),(select top 1 createon from tb where DateDiff(dd,createon,GetDate()) = 7),120)
/*
t1 t2 t3 t4 t5 t6 t7 t8
-------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
今天 昨天 星期二 星期一 星期日 星期六 星期五 一周前
2010-04-15 16:27:32 2010-04-14 16:27:32 2010-04-13 16:27:32 2010-04-12 16:27:32 2010-04-11 16:27:32 2010-04-10 16:27:32 2010-04-09 16:27:32 2010-04-08 16:27:32
(所影响的行数为 2 行)
*/
drop table tb
select createon, case when DateDiff(day,createon,GetDate())=0 then '今天'
when DateDiff(day,createon,GetDate())=1 then '昨天'
when DateDiff(day,createon,GetDate()) between 2 and 6 then replace(datename(dw,createon),'星期','周')
when DateDiff(day,createon,getdate())>=7 then '一周前' end
from tb
where createon<dateadd(day,DateDiff(day,0,getdate()),1)
createon
----------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2010-04-15 16:27:32.000 今天
2010-04-14 16:27:32.000 昨天
2010-04-13 16:27:32.000 周二
2010-04-12 16:27:32.000 周一
2010-04-11 16:27:32.000 周日
2010-04-10 16:27:32.000 周六
2010-04-09 16:27:32.000 周五
2010-04-08 16:27:32.000 一周前
2010-04-07 16:27:32.000 一周前
2010-03-15 16:27:32.000 一周前
2010-03-14 16:27:32.000 一周前
2010-03-13 16:27:32.000 一周前
2010-03-12 16:27:32.000 一周前
2010-04-11 16:27:32.000 周日
(14 行受影响)
select case when DateDiff(dd,createon,GetDate())=1 then '昨天'
when DateDiff(dd,createon,GetDate())=0 then '今天'
when DateDiff(dd,createon,dateadd(wk,datediff(wk,0,getdate()),0))=0 then '星期一'
when DateDiff(dd,createon,dateadd(wk,datediff(wk,0,getdate()),-1))=0 then '星期日'
when DateDiff(dd,createon,dateadd(wk,datediff(wk,0,getdate()),-2))=0 then '星期六'
when DateDiff(dd,createon,dateadd(wk,datediff(wk,0,getdate()),-3))=0 then '星期五'
when DateDiff(dd,createon,dateadd(wk,datediff(wk,0,getdate()),-4))=0 then '星期四'
when DateDiff(dd,createon,dateadd(wk,datediff(wk,0,getdate()),-5))=0 then '星期三'
when DateDiff(dd,createon,dateadd(wk,datediff(wk,0,getdate()),-6))=0 then '星期二'
when DateDiff(day,createon,getdate())>7 then '一周前' end as createon1
from T_Message order by createon desc