22,209
社区成员
发帖
与我相关
我的任务
分享
IF OBJECT_ID('[order]') IS NOT NULL
DROP TABLE [order]
GO
CREATE TABLE [order](orderID INT,account VARCHAR(1),amount INT,orderTime DATETIME)
INSERT [order] SELECT 1,'A',30,'2014-05-13 22:26:36.000' UNION ALL SELECT
2,'B',27,'2014-05-14 12:26:36.000' UNION ALL SELECT
3,'C',100,'2014-05-15 02:26:36.000' UNION ALL SELECT
4,'D',40,'2014-05-16 13:26:36.000'
---------------------------------------------------------查询------------------------------------------------------------------------------
DECLARE @NowDate AS DATETIME
SET @NowDate='2014-06-04 00:00:00' --当前时间
SELECT * FROM [order] WHERE CONVERT(VARCHAR,orderTime,23)<CONVERT(VARCHAR,@NowDate,23)+' 23:59:59' AND orderTime>(CASE WHEN DAY(@NowDate)<15 THEN CONVERT(VARCHAR(7),DATEADD(mm,-1,@NowDate),120)+'-15 '+'00:00:00' ELSE CONVERT(VARCHAR(7),@NowDate,120)+'-15 '+'00:00:00' end)
---------------------------------------------------------结果------------------------------------------------------------------------------
/*
orderID account amount orderTime
----------- ------- ----------- -----------------------
3 C 100 2014-05-15 02:26:36.000
4 D 40 2014-05-16 13:26:36.000
(2 行受影响)
*/
IF OBJECT_ID('[order]') IS NOT NULL
DROP TABLE [order]
GO
CREATE TABLE [order](orderID INT,account VARCHAR(1),amount INT,orderTime DATETIME)
INSERT [order] SELECT 1,'A',30,'2014-05-13 22:26:36.000' UNION ALL SELECT
2,'B',27,'2014-05-14 12:26:36.000' UNION ALL SELECT
3,'C',100,'2014-05-15 02:26:36.000' UNION ALL SELECT
4,'D',40,'2014-05-16 13:26:36.000'
---------------------------------------------------------查询------------------------------------------------------------------------------
DECLARE @NowDate AS DATETIME
SET @NowDate='2014-06-04 00:00:00' --当前时间
SELECT * FROM [order] WHERE CONVERT(VARCHAR,orderTime,23)<CONVERT(VARCHAR,@NowDate,23)+' 23:59:59' AND orderTime>(CASE WHEN DAY(@NowDate)<15 THEN CONVERT(VARCHAR(7),DATEADD(mm,-1,@NowDate),120)+'-15 '+CONVERT(VARCHAR,DATEADD(mm,-1,@NowDate),24) ELSE CONVERT(VARCHAR(7),@NowDate,120)+'-15 '+'00:00:00' end)
---------------------------------------------------------结果------------------------------------------------------------------------------
/*
orderID account amount orderTime
----------- ------- ----------- -----------------------
3 C 100 2014-05-15 02:26:36.000
4 D 40 2014-05-16 13:26:36.000
(2 行受影响)
*/
select * from tablename t1
where date> (select case when DAY(GETDATE())<15 then CONVERT(varchar(7),DATEADD(m,-1,getdate()),120)+'-15' else CONVERT(varchar(7),getdate(),120)+'-15' end date)
select * from order where ordertime>='2014-5-15' and ordertime<cast((convert(char(10),DateAdd(day,1,GetDate()),120) as datetime)
select * from order where ordertime>='2014-5-15'