按半小时规整
如 2008-2-2 12:12:13 => 2008-2-2 12:00:00
2008-3-4 9:45:28 => 2008-3-4 9:30:00
这个SQL中怎么写?
如 2008-2-2 12:12:13 => 2008-2-2 12:00:00
2008-3-4 9:45:28 => 2008-3-4 9:30:00
这个SQL中怎么写?
解决方案 »
- 同一个库,同样的语句,不同的表,一个报错,一个正常
- 是关于打SQL数据库失败的问题。
- ?sql server 2000 设置自动加1 的问题?
- sql server 2005:用户添加中的schema这个概念完全把我弄糊涂了
- 使用TransactionScope访问2005数据库MSDTC错误
- 关于sql查询,求教大神
- 不会写这个查询了!谢谢帮忙
- 好久不来了,给大家送分:)
- 我从其它机器上拷了一个SQL数据库文件xxx.mdf,xxx.ldf,拷到我机器里SQL server的DATA目录下,但打开SQL后看不到这个XXX.MDF库,怎么办?
- 两个数据库取数据的问题,高手大侠帮帮我,谢谢
- 谁能帮我解释一下这条特殊的数据库连接
- 关于SQL语句的问题
select dateadd(minute,datediff(minute,0,@dt)/30*30,0)
-- 2008-02-02 12:00:00.000set @dt = '2008-3-4 9:45:28 '
select dateadd(minute,datediff(minute,0,@dt)/30*30,0)
-- 2008-03-04 09:30:00.000
select dateadd(minute, datediff(minute,convert(varchar(8),@date,112),@date)/30*30, convert(varchar(8),@date,112))
/*
2008-02-02 12:00:00.000
*/set @date='2008-3-4 9:45:28'
select dateadd(minute, datediff(minute,convert(varchar(8),@date,112),@date)/30*30, convert(varchar(8),@date,112))
/*
2008-03-04 09:30:00.000
*/
set @date=getdate()
if datepart(minute,@date)>=30
select cast(left(@date,14)+'30:00.000' as datetime)
else
select cast(left(@date,14)+'00:00.000' as datetime)
--测试了一下,4楼与9楼的稳定性好
declare @dt datetimeset @dt = '5983-3-4 9:45:28 '
select @dtselect dateadd(minute,datediff(minute,0,@dt)/30*30,0)select dateadd(minute, datediff(minute,convert(varchar(8),@dt,112),@dt)/30*30, convert(varchar(8),@dt,112))
if datepart(minute,@dt)>=30
select cast(left(@dt,14)+'30:00.000' as datetime)
else
select cast(left(@dt,14)+'00:00.000' as datetime)set @dt = '1799-1-1 9:45:28 '
select @dtselect dateadd(minute,datediff(minute,0,@dt)/30*30,0)select dateadd(minute, datediff(minute,convert(varchar(8),@dt,112),@dt)/30*30, convert(varchar(8),@dt,112))if datepart(minute,@dt)>=30
select cast(left(@dt,14)+'30:00.000' as datetime)
else
select cast(left(@dt,14)+'00:00.000' as datetime)
select dateadd(minute,datediff(minute,0,@dt)/30*30,0)
-- 1900-01-01 00:00:00.000set @dt = '2999-12-31 23:45'
select dateadd(minute,datediff(minute,0,@dt)/30*30,0)
-- 2999-12-31 23:30:00.000-- ==================================================
-- 以下为找到的有效临界点set @dt = '1760-1-1 0:0:13'
select dateadd(minute,datediff(minute,0,@dt)/30*30,0)
-- 1760-01-01 00:00:00.000set @dt = '5982-12-31 23:45'
select dateadd(minute,datediff(minute,0,@dt)/30*30,0)
-- 5982-12-31 23:30:00.000-- ==================================================
select dateadd(minute,datediff(minute,0,@dt)/30*30,0)
-- 1900-01-01 00:00:00.000set @dt = '2999-12-31 23:45'
select dateadd(minute,datediff(minute,0,@dt)/30*30,0)
-- 2999-12-31 23:30:00.000-- ==================================================
-- 以下为找到的有效临界点set @dt = '1760-1-1 0:0:13'
select dateadd(minute,datediff(minute,0,@dt)/30*30,0)
-- 1760-01-01 00:00:00.000set @dt = '5982-12-31 23:45'
select dateadd(minute,datediff(minute,0,@dt)/30*30,0)
-- 5982-12-31 23:30:00.000-- ==================================================
--------------------不能说是乱写,9楼的是最易理解的.
在实际查询中,需把if换为case