已知一个dateime类型变量(如果getdate()),
然后比较时间部分,如果 时间 大于 8:00 小于12:00 输出 "上午"如果 时间 大于 12:00 小于18:00 输出 "下午"如果 时间 大于 18:00 小于 8:00 输出 "晚上" 请写 sql 或存储过程 或自定义函数
谢谢各位大侠啊
然后比较时间部分,如果 时间 大于 8:00 小于12:00 输出 "上午"如果 时间 大于 12:00 小于18:00 输出 "下午"如果 时间 大于 18:00 小于 8:00 输出 "晚上" 请写 sql 或存储过程 或自定义函数
谢谢各位大侠啊
case when hour(getdate()) between 8 and 11 then '上午',
case when hour(getdate()) between 12 and 17 then '下午',
else '晚上'
--................
select
case
when datepart(hour,getdate()) between 8 and 11 then '上午'
when datepart(hour,getdate()) between 12 and 17 then '下午'
else '晚上'
end
case
when convert(varchar(4),getdate(),108) between '08:00' and '11:00' then '上午'
when convert(varchar(4),getdate(),108) between '12:00' and '18:00' then '下午'
else '晚上'
end
select
case
when CONVERT(varchar(5),GETDATE(),108) between '8:00' and '12:00' then '上午'
when CONVERT(varchar(5),GETDATE(),108) between '12:00' and '18:30' then '下午'
else '晚上'
end
(@dt datetime)
returns nchar(2)
as
begin
declare @t nchar(2)
set @t=(case when {fn Hour(@dt)} between 8 and 12 then '上午'
when {fn Hour(@dt)} between 8 and 18 then '下午'
else '晚上'
end)
return @t
end
go
select dbo.settime('2011-11-18 15:15:15')
/*
----
下午(1 行受影响)*/
go
drop function dbo.settime
ALTER FUNCTION DBO.GETDATESTR(@DT DATETIME)
RETURNS NVARCHAR(20)
BEGIN
DECLARE @HOUR INTSET @HOUR = CONVERT(INT,CONVERT(NVARCHAR(2),@DT,24))
IF(@HOUR>=8 and @HOUR<12)
RETURN '上午'
IF(@HOUR>=12 and @HOUR<18)
RETURN '下午'
RETURN '晚上'
END
SELECT DBO.GETDATESTR(GETDATE())
建议直接case when
(@dt datetime)
returns nchar(2)
as
begin
declare @t nchar(2)
set @t=(case when convert(varchar(5),@dt,114) between '08:00' and '12:00' then '上午'
when convert(varchar(5),@dt,114) between '12:00' and '18:30' then '下午'
else '晚上'
end)
return @t
end
go
select dbo.settime('2011-11-18 15:15:15')
/*
----
下午(1 行受影响)*/
go
drop function dbo.settime
case
when convert(varchar(4),getdate(),108) between '08:00' and '11:00' then '上午'
when convert(varchar(4),getdate(),108) between '12:00' and '18:00' then '下午'
else '晚上'
end
when datepart(hour,GETDATE())>12 and datepart(hour,GETDATE())<18 then N'下午'
when datepart(hour,GETDATE())>18 OR datepart(hour,GETDATE())<8 then N'晚上'
else '' end
@dt DATETIME
)RETURNS NVARCHAR(2)
AS
begin
RETURN
(
case
when convert(varchar(2),@dt,108) BETWEEN '08' and '11' then N'上午'
when convert(varchar(2),@dt,108) BETWEEN '12' and '17' then N'下午'
else N'晚上' end
)
END
goDECLARE @dt DATETIME
SET @dt=GETDATE()
SELECT dbo.fn_date(@dt),@dt/*下午 2011-11-18 16:20:23.060
*/
Create function fn(
@Date datetime
)
returns nvarchar(2)
as
Declare @s nvarchar(2)
If (Select datepart(hh,@Date))<8
Set @s=N'晚上'
Else If (Select datepart(hh,@Date))<12
Set @s=N'早上'
Else If (Select datepart(hh,@Date))<18
Set @s=N'下午'
Else
Set @s=N'晚上'return @s
go
case
when convert(varchar(10),getdate(),108) between '08:00:00' and '11:00:00' then '上午'
when convert(varchar(10),getdate(),108) between '12:00:00'and '18:00:00' then '下午'
else '晚上'
end
if object_id('uf_test','FN') is not null
drop function uf_test
go
create function uf_test (@dt datetime)
returns varchar(10)
as
begin
declare @str varchar(10)
if datepart(hh,@dt)>=8 and datepart(hh,@dt)<12
set @str = '上午'
else if datepart(hh,@dt)>=12 and datepart(hh,@dt)<18
set @str = '下午'
else
set @str = '晚上'
return @str
end
go
select dbo.uf_test('2011-11-11 08:01:01') --上午
select dbo.uf_test('2011-11-11 12:01:01') --下午
select dbo.uf_test('2011-11-11 18:01:01') --晚上
/*
----------
上午(1 行受影响)
----------
下午(1 行受影响)
----------
晚上(1 行受影响)*/