--假设Test表数据如下: CREATE TABLE dbo.Test ( t datetime ,a int identity ) INSERT INTO dbo.Test(t) VALUES('2009-01-06 12:10:09') INSERT INTO dbo.Test(t) VALUES('2009-05-07 12:10:09') INSERT INTO dbo.Test(t) VALUES('2009-07-01 12:10:09') INSERT INTO dbo.Test(t) VALUES('2009-07-15 12:10:09') INSERT INTO dbo.Test(t) VALUES('2009-07-27 12:10:09')(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)--查询相差10天 select t from Test where DATEDIFF(DAY,t,GETDATE())=10 --查询相差10-20天 select t from Test where DATEDIFF(DAY,t,GETDATE()) between 10 and 20 -- Returns: 2009-07-15 12:10:09.000 --查询相差20-30天 select t from Test where DATEDIFF(DAY,t,GETDATE()) between 20 and 30 -- Returns: 2009-07-01 12:10:09.000 --查询相差30天以上 select t from Test where DATEDIFF(DAY,t,GETDATE()) > 30 -- Returns: 2009-01-06 12:10:09.000 2009-05-07 12:10:09.000
楼主说是varchar类型的,还需要转换CREATE TABLE test ( t varchar(30) ) INSERT INTO Test(t) VALUES('2009-01-06 12:10:09') INSERT INTO Test(t) VALUES('2009-05-07 12:10:09') INSERT INTO Test(t) VALUES('2009-07-01 12:10:09') INSERT INTO Test(t) VALUES('2009-07-15 12:10:09') INSERT INTO Test(t) VALUES('2009-07-27 12:10:09')--查询相差10天 select * from Test where datediff(day,convert(datetime,t),getdate()) = 10 --查询相差10-20天 select * from Test where datediff(day,convert(datetime,t),getdate()) between 10 and 20 --查询相差20-30天 select * from Test where datediff(day,convert(datetime,t),getdate()) between 20 and 30 --查询相差30天以上 select * from Test where datediff(day,convert(datetime,t),getdate()) > 30/** t ------------------------------ (所影响的行数为 0 行)t ------------------------------ 2009-07-15 12:10:09(所影响的行数为 1 行)t ------------------------------ 2009-07-01 12:10:09(所影响的行数为 1 行)t ------------------------------ 2009-01-06 12:10:09 2009-05-07 12:10:09(所影响的行数为 2 行)**/
If not object_id('tb') is null Drop table tb Go CREATE TABLE tb(t varchar(10)) insert tb select '2009-01-21' union all select '2009-01-21' union all select '2009-02-21' union all select '2009-02-21' union all select '2009-03-21' union all select '2009-03-21' union all select '2009-04-21' union all select '2009-04-21' union all select '2009-05-21' union all select '2009-05-21' union all select '2009-06-21' union all select '2009-06-21' union all select '2009-07-02' union all select '2009-07-10' union all select '2009-07-21' union all select '2009-07-21' go --查询相差10天 select count(1) from tb where datediff(dd,t,getdate())=10 --2 --查询相差10-20天 select count(1) from tb where datediff(dd,t,getdate()) between 10 and 20 --2 --查询相差20-30天 select count(1) from tb where datediff(dd,t,getdate()) between 20 and 30 --2 --查询相差30天以上 select count(1) from tb where datediff(dd,t,getdate())>30 --12
用 datediff(dd,cast(t as datetime),getdate()) 或者 datediff(dd,convert(datetime,t),getdate())
-- 借用1楼数据 if object_id('test') is not null drop table test CREATE TABLE dbo.Test ( t datetime ,a int identity ) INSERT INTO dbo.Test(t) VALUES('2009-01-06 12:10:09') INSERT INTO dbo.Test(t) VALUES('2009-05-07 12:10:09') INSERT INTO dbo.Test(t) VALUES('2009-07-01 12:10:09') INSERT INTO dbo.Test(t) VALUES('2009-07-15 12:10:09') INSERT INTO dbo.Test(t) VALUES('2009-07-27 12:10:09')select [差10天]=sum(case when datediff(day,t,getdate())=10 then 1 else 0 end), [差10-20天]=sum(case when datediff(day,t,getdate()) between 10 and 20 then 1 else 0 end), [差20-30天]=sum(case when datediff(day,t,getdate()) between 20 and 30 then 1 else 0 end) from test
if object_id('test') is not null drop table test CREATE TABLE dbo.Test ( t datetime ,a int identity ) INSERT INTO dbo.Test(t) VALUES('2009-01-06 12:10:09') -- >30 INSERT INTO dbo.Test(t) VALUES('2009-05-07 12:10:09') -- >30 INSERT INTO dbo.Test(t) VALUES('2009-07-01 12:10:09') -- 20-30 INSERT INTO dbo.Test(t) VALUES('2009-07-15 12:10:09') -- 10-20 INSERT INTO dbo.Test(t) VALUES('2009-07-27 12:10:09') -- <10 select [差10天]=sum(case when datediff(day,t,getdate())=10 then 1 else 0 end), [差10-20天]=sum(case when datediff(day,t,getdate()) between 10 and 20 then 1 else 0 end), [差20-30天]=sum(case when datediff(day,t,getdate()) between 20 and 30 then 1 else 0 end), [大于30天]=sum(case when datediff(day,t,getdate())>30 then 1 else 0 end) from test差10天 差10-20天 差20-30天 大于30天 ----------- ----------- ----------- ----------- 0 1 1 2(所影响的行数为 1 行)
-- t是字符型,则改一下: select [差10天]=sum(case when datediff(day,cast(t as datetime),getdate())=10 then 1 else 0 end), [差10-20天]=sum(case when datediff(day,cast(t as datetime),getdate()) between 10 and 20 then 1 else 0 end), [差20-30天]=sum(case when datediff(day,cast(t as datetime),getdate()) between 20 and 30 then 1 else 0 end), [大于30天]=sum(case when datediff(day,cast(t as datetime),getdate())>30 then 1 else 0 end) from test
select t as [相差10天] from Test where DATEDIFF(DAY,t,GETDATE())=10select t as [相差10-20天] from Test where DATEDIFF(DAY,t,GETDATE()) between 10 and 20select t as [相差20-30天] from Test where DATEDIFF(DAY,t,GETDATE()) between 20 and 30select t as [相差30天以上] from Test where DATEDIFF(DAY,t,GETDATE()) > 30
if object_id('test') is not null drop table test go CREATE TABLE dbo.Test ( t varchar(10) ,a int identity ) INSERT INTO dbo.Test(t) VALUES('2009-01-06') INSERT INTO dbo.Test(t) VALUES('2009-05-07') INSERT INTO dbo.Test(t) VALUES('2009-07-01') INSERT INTO dbo.Test(t) VALUES('2009-07-15') INSERT INTO dbo.Test(t) VALUES('2009-07-27')select [差10天]=sum(case when datediff(day,t,getdate())=10 then 1 else 0 end), [差10-20天]=sum(case when datediff(day,t,getdate()) between 10 and 20 then 1 else 0 end), [差20-30天]=sum(case when datediff(day,t,getdate()) between 20 and 30 then 1 else 0 end), [大于30天]=sum(case when datediff(day,t,getdate())>30 then 1 else 0 end)from test /* 差10天 差10-20天 差20-30天 大于30天 ----------- ----------- ----------- ----------- 0 1 1 2 (1 行受影响) */
SELECT * FROM ( SELECT CASE DateDiff(Day, CreateTime, GETDATE()) / 10 WHEN 0 THEN '10天内' WHEN 1 THEN '10-19天' WHEN 2 THEN '20-29天' ELSE '30天以上' END AS FD, ID FROM OrderDetail WHERE CreateTime <= GETDATE() ) A PIVOT ( COUNT(ID) FOR FD IN ([10天内],[10-19天],[20-29天],[30天以上]) ) B
VARCHAR格式的日期在函数应用时不需要转换,只是分秒全为0,在本例中不影响查询结果。
--查询相差10天 select * from Test where datediff(day,convert(datetime,t),getdate()) = 10 --查询相差10-20天 select * from Test where datediff(day,convert(datetime,t),getdate()) between 10 and 20 --查询相差20-30天 select * from Test where datediff(day,convert(datetime,t),getdate()) between 20 and 30 --查询相差30天以上 select * from Test where datediff(day,convert(datetime,t),getdate()) > 30
CREATE TABLE dbo.Test
(
t datetime
,a int identity
)
INSERT INTO dbo.Test(t) VALUES('2009-01-06 12:10:09')
INSERT INTO dbo.Test(t) VALUES('2009-05-07 12:10:09')
INSERT INTO dbo.Test(t) VALUES('2009-07-01 12:10:09')
INSERT INTO dbo.Test(t) VALUES('2009-07-15 12:10:09')
INSERT INTO dbo.Test(t) VALUES('2009-07-27 12:10:09')(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)--查询相差10天
select t from Test where DATEDIFF(DAY,t,GETDATE())=10
--查询相差10-20天
select t from Test where DATEDIFF(DAY,t,GETDATE()) between 10 and 20
-- Returns:
2009-07-15 12:10:09.000
--查询相差20-30天
select t from Test where DATEDIFF(DAY,t,GETDATE()) between 20 and 30
-- Returns:
2009-07-01 12:10:09.000
--查询相差30天以上
select t from Test where DATEDIFF(DAY,t,GETDATE()) > 30
-- Returns:
2009-01-06 12:10:09.000
2009-05-07 12:10:09.000
(
t varchar(30)
)
INSERT INTO Test(t) VALUES('2009-01-06 12:10:09')
INSERT INTO Test(t) VALUES('2009-05-07 12:10:09')
INSERT INTO Test(t) VALUES('2009-07-01 12:10:09')
INSERT INTO Test(t) VALUES('2009-07-15 12:10:09')
INSERT INTO Test(t) VALUES('2009-07-27 12:10:09')--查询相差10天
select * from Test where datediff(day,convert(datetime,t),getdate()) = 10
--查询相差10-20天
select * from Test where datediff(day,convert(datetime,t),getdate()) between 10 and 20
--查询相差20-30天
select * from Test where datediff(day,convert(datetime,t),getdate()) between 20 and 30
--查询相差30天以上
select * from Test where datediff(day,convert(datetime,t),getdate()) > 30/**
t
------------------------------ (所影响的行数为 0 行)t
------------------------------
2009-07-15 12:10:09(所影响的行数为 1 行)t
------------------------------
2009-07-01 12:10:09(所影响的行数为 1 行)t
------------------------------
2009-01-06 12:10:09
2009-05-07 12:10:09(所影响的行数为 2 行)**/
Drop table tb
Go
CREATE TABLE tb(t varchar(10))
insert tb
select '2009-01-21' union all
select '2009-01-21' union all
select '2009-02-21' union all
select '2009-02-21' union all
select '2009-03-21' union all
select '2009-03-21' union all
select '2009-04-21' union all
select '2009-04-21' union all
select '2009-05-21' union all
select '2009-05-21' union all
select '2009-06-21' union all
select '2009-06-21' union all
select '2009-07-02' union all
select '2009-07-10' union all
select '2009-07-21' union all
select '2009-07-21'
go
--查询相差10天
select count(1) from tb where datediff(dd,t,getdate())=10 --2
--查询相差10-20天
select count(1) from tb where datediff(dd,t,getdate()) between 10 and 20 --2
--查询相差20-30天
select count(1) from tb where datediff(dd,t,getdate()) between 20 and 30 --2
--查询相差30天以上
select count(1) from tb where datediff(dd,t,getdate())>30 --12
或者 datediff(dd,convert(datetime,t),getdate())
-- 借用1楼数据
if object_id('test') is not null drop table test
CREATE TABLE dbo.Test
(
t datetime
,a int identity
)
INSERT INTO dbo.Test(t) VALUES('2009-01-06 12:10:09')
INSERT INTO dbo.Test(t) VALUES('2009-05-07 12:10:09')
INSERT INTO dbo.Test(t) VALUES('2009-07-01 12:10:09')
INSERT INTO dbo.Test(t) VALUES('2009-07-15 12:10:09')
INSERT INTO dbo.Test(t) VALUES('2009-07-27 12:10:09')select [差10天]=sum(case when datediff(day,t,getdate())=10 then 1 else 0 end),
[差10-20天]=sum(case when datediff(day,t,getdate()) between 10 and 20 then 1 else 0 end),
[差20-30天]=sum(case when datediff(day,t,getdate()) between 20 and 30 then 1 else 0 end)
from test
if object_id('test') is not null drop table test
CREATE TABLE dbo.Test
(
t datetime
,a int identity
)
INSERT INTO dbo.Test(t) VALUES('2009-01-06 12:10:09') -- >30
INSERT INTO dbo.Test(t) VALUES('2009-05-07 12:10:09') -- >30
INSERT INTO dbo.Test(t) VALUES('2009-07-01 12:10:09') -- 20-30
INSERT INTO dbo.Test(t) VALUES('2009-07-15 12:10:09') -- 10-20
INSERT INTO dbo.Test(t) VALUES('2009-07-27 12:10:09') -- <10 select [差10天]=sum(case when datediff(day,t,getdate())=10 then 1 else 0 end),
[差10-20天]=sum(case when datediff(day,t,getdate()) between 10 and 20 then 1 else 0 end),
[差20-30天]=sum(case when datediff(day,t,getdate()) between 20 and 30 then 1 else 0 end),
[大于30天]=sum(case when datediff(day,t,getdate())>30 then 1 else 0 end)
from test差10天 差10-20天 差20-30天 大于30天
----------- ----------- ----------- -----------
0 1 1 2(所影响的行数为 1 行)
select [差10天]=sum(case when datediff(day,cast(t as datetime),getdate())=10 then 1 else 0 end),
[差10-20天]=sum(case when datediff(day,cast(t as datetime),getdate()) between 10 and 20 then 1 else 0 end),
[差20-30天]=sum(case when datediff(day,cast(t as datetime),getdate()) between 20 and 30 then 1 else 0 end),
[大于30天]=sum(case when datediff(day,cast(t as datetime),getdate())>30 then 1 else 0 end)
from test
from Test
where DATEDIFF(DAY,t,GETDATE())=10select t as [相差10-20天]
from Test
where DATEDIFF(DAY,t,GETDATE()) between 10 and 20select t as [相差20-30天]
from Test
where DATEDIFF(DAY,t,GETDATE()) between 20 and 30select t as [相差30天以上]
from Test
where DATEDIFF(DAY,t,GETDATE()) > 30
drop table test
go
CREATE TABLE dbo.Test
(
t varchar(10)
,a int identity
)
INSERT INTO dbo.Test(t) VALUES('2009-01-06')
INSERT INTO dbo.Test(t) VALUES('2009-05-07')
INSERT INTO dbo.Test(t) VALUES('2009-07-01')
INSERT INTO dbo.Test(t) VALUES('2009-07-15')
INSERT INTO dbo.Test(t) VALUES('2009-07-27')select [差10天]=sum(case when datediff(day,t,getdate())=10 then 1 else 0 end),
[差10-20天]=sum(case when datediff(day,t,getdate()) between 10 and 20 then 1 else 0 end),
[差20-30天]=sum(case when datediff(day,t,getdate()) between 20 and 30 then 1 else 0 end),
[大于30天]=sum(case when datediff(day,t,getdate())>30 then 1 else 0 end)from test
/*
差10天 差10-20天 差20-30天 大于30天
----------- ----------- ----------- -----------
0 1 1 2
(1 行受影响)
*/
FROM (
SELECT CASE DateDiff(Day, CreateTime, GETDATE()) / 10
WHEN 0 THEN '10天内'
WHEN 1 THEN '10-19天'
WHEN 2 THEN '20-29天'
ELSE '30天以上'
END AS FD, ID
FROM OrderDetail
WHERE CreateTime <= GETDATE()
) A
PIVOT (
COUNT(ID) FOR FD IN ([10天内],[10-19天],[20-29天],[30天以上])
) B
select * from Test where datediff(day,convert(datetime,t),getdate()) = 10
--查询相差10-20天
select * from Test where datediff(day,convert(datetime,t),getdate()) between 10 and 20
--查询相差20-30天
select * from Test where datediff(day,convert(datetime,t),getdate()) between 20 and 30
--查询相差30天以上
select * from Test where datediff(day,convert(datetime,t),getdate()) > 30