表名 mytest tid是主键
tid AID DTM( 生成时间) NAME(姓名)
1 1 2008-07-28 09:30:52.200 王小
2 2 2008-07-28 14:49:10.357 王小
3 3 2008-07-28 15:12:31.937 王小
4 4 2008-07-28 16:07:23.217 王小
5 3 2008-07-28 17:01:43.747 李刚
6 3 2008-07-30 15:55:16.827 李刚
7 3 2008-07-31 19:48:54.653 李刚
8 3 2008-08-01 10:23:05.187 李刚
9 3 2008-08-01 11:10:06.450 李刚想查询本月的记录
SQL 语句如下:
select count(*) from mytest WHERE AID='3' and DTM>=CONVERT(VARCHAR,GETDATE(),120)结果不正确,红色部分该如何写呢?
tid AID DTM( 生成时间) NAME(姓名)
1 1 2008-07-28 09:30:52.200 王小
2 2 2008-07-28 14:49:10.357 王小
3 3 2008-07-28 15:12:31.937 王小
4 4 2008-07-28 16:07:23.217 王小
5 3 2008-07-28 17:01:43.747 李刚
6 3 2008-07-30 15:55:16.827 李刚
7 3 2008-07-31 19:48:54.653 李刚
8 3 2008-08-01 10:23:05.187 李刚
9 3 2008-08-01 11:10:06.450 李刚想查询本月的记录
SQL 语句如下:
select count(*) from mytest WHERE AID='3' and DTM>=CONVERT(VARCHAR,GETDATE(),120)结果不正确,红色部分该如何写呢?
解决方案 »
- 如何将delete 和 insert 写在一个sql语句中啊?
- 使用DISTINCT如何去掉重复,并查询所有字段?
- 如何自动汇总子级数额累加至上级及最上级?
- 在sql2000的查询分析器 中文乱码
- opendatasource的参数如何使用变量
- 重金求解决方法,一个验证用户登录的存储过程有时会执行超时
- SQL Server 里面什么类型的数据可以同时保存取出日期 和时间呢?
- 如何这样进行选择?
- 添加触发器后产生的问题
- 在SQL SERVER中如何在表中间插入一条记录
- 请帮忙看看这条T-SQL哪里写错了
- 在一个触发器中如何判断是更新了数据还是插入了新数据???????????????????????????????
datediff(month,dtm,'2007-01-01')=0
datediff(month,dtm,'2008-01-31')=0 --当月中的任意一个日期
INSERT INTO mytest
SELECT '1','1','2008-07-28 09:30:52.200','王小' UNION ALL
SELECT '2','2','2008-07-28 14:49:10.357','王小' UNION ALL
SELECT '3','3','2008-07-28 15:12:31.937','王小' UNION ALL
SELECT '4','4','2008-07-28 16:07:23.217','王小' UNION ALL
SELECT '5','3','2008-07-28 17:01:43.747','李刚' UNION ALL
SELECT '6','3','2008-07-30 15:55:16.827','李刚' UNION ALL
SELECT '7','3','2008-07-31 19:48:54.653','李刚' UNION ALL
SELECT '8','3','2008-08-01 10:23:05.187','李刚' UNION ALL
SELECT '9','3','2008-08-01 11:10:06.450','李刚' UNION ALL
SELECT '10','3','2008-08-05 11:10:06.450','李刚' --增加当表里面有大于系统当前日期时间的测试数据GOSELECT *
FROM mytest WHERE AID='3' AND
DTM>=CAST(CONVERT(VARCHAR(7),GETDATE(),120)+'-'+'01' AS DATETIME) AND
DTM<=DATEADD(dd,-1,CAST(CONVERT(VARCHAR(7),DATEADD(mm,1,GETDATE()),120)+'-'+'01' AS DATETIME))
GO
/* 测试结果
tid AID DTM NAME
----------- ---------- ----------------------- --------------------
8 3 2008-08-01 10:23:05.187 李刚
9 3 2008-08-01 11:10:06.450 李刚
10 3 2008-08-05 11:10:06.450 李刚(3 行受影响)
*/SELECT COUNT(*)
FROM mytest WHERE AID='3' AND
DTM>=CAST(CONVERT(VARCHAR(7),GETDATE(),120)+'-'+'01' AS DATETIME) AND
DTM<=DATEADD(dd,-1,CAST(CONVERT(VARCHAR(7),DATEADD(mm,1,GETDATE()),120)+'-'+'01' AS DATETIME))
GO
/* 测试结果
-----------
3
(1 行受影响)
*/DROP TABLE mytest
GO
INSERT INTO mytest
SELECT '1','1','2008-07-28 09:30:52.200','王小' UNION ALL
SELECT '2','2','2008-07-28 14:49:10.357','王小' UNION ALL
SELECT '3','3','2008-07-28 15:12:31.937','王小' UNION ALL
SELECT '4','4','2008-07-28 16:07:23.217','王小' UNION ALL
SELECT '5','3','2008-07-28 17:01:43.747','李刚' UNION ALL
SELECT '6','3','2008-07-30 15:55:16.827','李刚' UNION ALL
SELECT '7','3','2008-07-31 19:48:54.653','李刚' UNION ALL
SELECT '8','3','2008-08-01 10:23:05.187','李刚' UNION ALL
SELECT '9','3','2008-08-01 11:10:06.450','李刚' UNION ALL
SELECT '10','3','2008-08-05 11:10:06.450','李刚' --增加当表里面有大于系统当前日期时间的测试数据GOSELECT *
FROM mytest WHERE AID='3' AND
DTM>=CAST(CONVERT(VARCHAR(7),GETDATE(),120)+'-'+'01' AS DATETIME) AND
DTM<=DATEADD(dd,-1,CAST(CONVERT(VARCHAR(7),DATEADD(mm,1,GETDATE()),120)+'-'+'01' AS DATETIME))
GO
/* 测试结果
tid AID DTM NAME
----------- ---------- ----------------------- --------------------
8 3 2008-08-01 10:23:05.187 李刚
9 3 2008-08-01 11:10:06.450 李刚
10 3 2008-08-05 11:10:06.450 李刚(3 行受影响)
*/SELECT COUNT(*)
FROM mytest WHERE AID='3' AND
DTM>=CAST(CONVERT(VARCHAR(7),GETDATE(),120)+'-'+'01' AS DATETIME) AND
DTM<=DATEADD(dd,-1,CAST(CONVERT(VARCHAR(7),DATEADD(mm,1,GETDATE()),120)+'-'+'01' AS DATETIME))
GO
/* 测试结果
-----------
3
(1 行受影响)
*/DROP TABLE mytest
GO
CREATE TABLE mytest (tid INT,AID NVARCHAR(10),DTM DATETIME,[NAME] NVARCHAR(20) )
INSERT INTO mytest
SELECT '1','1','2008-07-28 09:30:52.200','王小' UNION ALL
SELECT '2','2','2008-07-28 14:49:10.357','王小' UNION ALL
SELECT '3','3','2008-07-28 15:12:31.937','王小' UNION ALL
SELECT '4','4','2008-07-28 16:07:23.217','王小' UNION ALL
SELECT '5','3','2008-07-28 17:01:43.747','李刚' UNION ALL
SELECT '6','3','2008-07-30 15:55:16.827','李刚' UNION ALL
SELECT '7','3','2008-07-31 19:48:54.653','李刚' UNION ALL
SELECT '8','3','2008-08-01 10:23:05.187','李刚' UNION ALL
SELECT '9','3','2008-08-01 11:10:06.450','李刚' UNION ALL
SELECT '10','3','2008-08-05 11:10:06.450','李刚' --增加当表里面有大于系统当前日期时间的测试数据GOSELECT *
FROM mytest WHERE AID='3' AND
DTM>=CAST(CONVERT(VARCHAR(7),GETDATE(),120)+'-'+'01' AS DATETIME) AND
DTM<=DATEADD(dd,-1,CAST(CONVERT(VARCHAR(7),DATEADD(mm,1,GETDATE()),120)+'-'+'01' AS DATETIME))
GO
/* 测试结果
tid AID DTM NAME
----------- ---------- ----------------------- --------------------
8 3 2008-08-01 10:23:05.187 李刚
9 3 2008-08-01 11:10:06.450 李刚
10 3 2008-08-05 11:10:06.450 李刚(3 行受影响)
*/SELECT COUNT(*)
FROM mytest WHERE AID='3' AND
DTM>=CAST(CONVERT(VARCHAR(7),GETDATE(),120)+'-01' AS DATETIME) AND
DTM<=DATEADD(dd,-1,CAST(CONVERT(VARCHAR(7),DATEADD(mm,1,GETDATE()),120)+'-01'+' 23:59:59' AS DATETIME))
GO
/* 测试结果
-----------
3
(1 行受影响)
*/DROP TABLE mytest
GO
Month()
Year()
Day()
当然,如果数据量很小的话,语句越简单越好!
改为:
DateDiff(month,dtm,getdate())=0