마감시간이 오전 8시 일 경우
함수에다가 현재 시간 스트링으로 넘겼을때
정산 날짜와 오전 오후를 알고 싶습니다.
정산 마감은 오전 8시
오전은 08:00 ~ 22:00
오후는 22:00 ~ 다음날 08:00
시로 기준을 정한다음에
2013-12-05 09:00 을 던졌을 경우에는 2013-12-05일과 오전
2013-12-06 07:00 을 던졌을 경우에는 2013-12-05일과 오후
의 결과값을 받고 싶습니다.
이런걸 해보지 못해서 너무 머리아프네요
부탁드리겠습니다 ^^
Comment 3
-
건우아빠
2013.12.05 10:24
-
없다
2013.12.05 13:40
with res as
(
select convert(datetime, '2013-12-04 00:00:01') Tday union all
select convert(datetime, '2013-12-04 01:11:57') Tday union all
select convert(datetime, '2013-12-04 02:11:57') Tday union all
select convert(datetime, '2013-12-04 03:11:57') Tday union all
select convert(datetime, '2013-12-04 04:11:57') Tday union all
select convert(datetime, '2013-12-04 05:11:57') Tday union all
select convert(datetime, '2013-12-04 06:11:57') Tday union all
select convert(datetime, '2013-12-04 07:11:57') Tday union all
select convert(datetime, '2013-12-04 08:11:57') Tday union all
select convert(datetime, '2013-12-04 09:11:57') Tday union all
select convert(datetime, '2013-12-04 10:11:57') Tday union all
select convert(datetime, '2013-12-04 11:11:57') Tday union all
select convert(datetime, '2013-12-04 12:11:57') Tday union all
select convert(datetime, '2013-12-04 13:11:57') Tday union all
select convert(datetime, '2013-12-04 14:11:57') Tday union all
select convert(datetime, '2013-12-04 15:11:57') Tday union all
select convert(datetime, '2013-12-04 16:11:57') Tday union all
select convert(datetime, '2013-12-04 17:11:57') Tday union all
select convert(datetime, '2013-12-04 18:11:57') Tday union all
select convert(datetime, '2013-12-04 19:11:57') Tday union all
select convert(datetime, '2013-12-04 20:11:57') Tday union all
select convert(datetime, '2013-12-04 21:11:57') Tday union all
select convert(datetime, '2013-12-04 22:11:57') Tday union all
select convert(datetime, '2013-12-04 23:59:57') Tday
)
select
tday,
case when convert(float , tday) - (floor(convert(float , tday)) + 28800.0/86400) < 0
then convert(char(10), DATEADD(dd ,-1, Tday), 121) + ' 오후'
else convert(char(10), tday, 121) + ' 오전'
end
from res
숫자로 바꾸면 쫌 나아요..ㅎ
더복잡한가?? ㅠㅠ
-
건우아빠
2013.12.05 14:23
숫자가 더 보기 좋네요....간략하고.
with res as
(
select convert(datetime, '2013-12-04 00:00:01') Tday union all
select convert(datetime, '2013-12-04 01:11:57') Tday union all
select convert(datetime, '2013-12-04 02:11:57') Tday union all
select convert(datetime, '2013-12-04 03:11:57') Tday union all
select convert(datetime, '2013-12-04 04:11:57') Tday union all
select convert(datetime, '2013-12-04 05:11:57') Tday union all
select convert(datetime, '2013-12-04 06:11:57') Tday union all
select convert(datetime, '2013-12-04 07:11:57') Tday union all
select convert(datetime, '2013-12-04 08:11:57') Tday union all
select convert(datetime, '2013-12-04 09:11:57') Tday union all
select convert(datetime, '2013-12-04 10:11:57') Tday union all
select convert(datetime, '2013-12-04 11:11:57') Tday union all
select convert(datetime, '2013-12-04 12:11:57') Tday union all
select convert(datetime, '2013-12-04 13:11:57') Tday union all
select convert(datetime, '2013-12-04 14:11:57') Tday union all
select convert(datetime, '2013-12-04 15:11:57') Tday union all
select convert(datetime, '2013-12-04 16:11:57') Tday union all
select convert(datetime, '2013-12-04 17:11:57') Tday union all
select convert(datetime, '2013-12-04 18:11:57') Tday union all
select convert(datetime, '2013-12-04 19:11:57') Tday union all
select convert(datetime, '2013-12-04 20:11:57') Tday union all
select convert(datetime, '2013-12-04 21:11:57') Tday union all
select convert(datetime, '2013-12-04 22:11:57') Tday union all
select convert(datetime, '2013-12-04 23:59:57') Tday
)
select Tday
, CONVERT(varchar(10) , Tday , 121) [일자]
, datepart(hour , Tday ) [시간]
, case when (datepart(hour , Tday ) between 0 and 8 )
or (datepart(hour , Tday ) between 22 and 23 )
then CONVERT(varchar(10) , DATEADD(dd ,-1, Tday), 121)
else CONVERT(varchar(10) , Tday , 121) end
+' '+
case when (datepart(hour , Tday ) between 0 and 8 )
or (datepart(hour , Tday ) between 22 and 23 )
then '오후'
else '오전' end [계산일시]
from res