给定一个日期,如'2009-06-01'
要求得出的结果是:
早班 2009-06-01 08:00:00.000 2009-06-01 19:59:59.997
晚班 2009-06-01 20:00:00.000 2009-06-02 07:59:59.997注意,日期是随意给定的。求一个能快速生成这种格式的方法。
要求得出的结果是:
早班 2009-06-01 08:00:00.000 2009-06-01 19:59:59.997
晚班 2009-06-01 20:00:00.000 2009-06-02 07:59:59.997注意,日期是随意给定的。求一个能快速生成这种格式的方法。
set @s='2009-06-01'
select 早班=convert(varchar(10),@s,120)+' 08:00:00.000 '+convert(varchar(10),@s,120)+' 19:59:59.997 ',
晚班=convert(varchar(10),@s,120)+' 20:00:00.000 '+convert(varchar(10),DATEADD(DD,1,@s),120)+' 07:59:59.997'/*
2009-06-01 08:00:00.000 2009-06-01 19:59:59.997 2009-06-01 20:00:00.000 2009-06-02 07:59:59.997
*/
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
declare @s datetime
set @s='2009-06-01'
select 早班=convert(varchar(10),@s,120)+' 08:00:00.000 '+convert(varchar(10),@s,120)+' 19:59:59.997 ',
晚班=convert(varchar(10),@s,120)+' 20:00:00.000 '+convert(varchar(10),DATEADD(DD,1,@s),120)+' 07:59:59.997'/*
早班 晚班
2009-06-01 08:00:00.000 2009-06-01 19:59:59.997 2009-06-01 20:00:00.000 2009-06-02 07:59:59.997
*/
select 早班=convert(varchar(10),给定的时间,120)+' 08:00:00.000 '+convert(varchar(10),给定的时间,120)+' 19:59:59.997 ',
晚班=convert(varchar(10),给定的时间,120)+' 20:00:00.000 '+convert(varchar(10),DATEADD(DD,1,给定的时间),120)+' 07:59:59.997'
SET @date = '2009-06-01';SELECT '早班' AS msg,DATEADD(hour,8,@date) AS beginTime,
DATEADD(second,-1,DATEADD(hour,20,@DATE)) AS endTime
UNION ALL
SELECT '晚班' AS msg,DATEADD(hour,20,@DATE) AS beginTime,
DATEADD(second,-1,DATEADD(hour,8,DATEADD(day,1,@date))) AS endTime/*
msg beginTime endTime
---- ----------------------- -----------------------
早班 2009-06-01 08:00:00.000 2009-06-01 19:59:59.000
晚班 2009-06-01 20:00:00.000 2009-06-02 07:59:59.000(2 行受影响)
*/
SET @DATE='2009-06-01'SELECT DATEADD(HH,8,@DATE),DATEADD(MS,-3, DATEADD(HH,8+12,@DATE))
UNION ALL
SELECT DATEADD(HH,8+12,@DATE),DATEADD(MS,-3, DATEADD(HH,20+12,@DATE))
/*
2009-06-01 08:00:00.000 2009-06-01 19:59:59.997
2009-06-01 20:00:00.000 2009-06-02 07:59:59.997
*/
set @date = '2009-06-01';
select '早班' as 班次,dateadd(hour,8,@date)开始时间,
dateadd(second,-1,dateadd(hour,20,@date))结束时间
union all
select '晚班' as msg,dateadd(hour,20,@date),
dateadd(second,-1,dateadd(hour,8,dateadd(day,1,@date)))班次 开始时间 结束时间
---- ----------------------- -----------------------
早班 2009-06-01 08:00:00.000 2009-06-01 19:59:59.000
晚班 2009-06-01 20:00:00.000 2009-06-02 07:59:59.000(2 行受影响)
-- =========================================
-- -----------t_mac 小编-------------
---希望有天成为大虾----
-- =========================================
declare @s datetime
set @s='2009-06-01'
select 班次='早班',上班时间=convert(varchar(10),@s,120)+' 08:00:00.000 '+convert(varchar(10),@s,120)+' 19:59:59.997 '
union all
select 班次='晚班' ,convert(varchar(10),@s,120)+' 20:00:00.000 '+convert(varchar(10),DATEADD(DD,1,@s),120)+' 07:59:59.997'/*
早班 2009-06-01 08:00:00.000 2009-06-01 19:59:59.997
晚班 2009-06-01 20:00:00.000 2009-06-02 07:59:59.997
*/
set @date = '2009-06-01';
select '早班' as 班次,dateadd(hour,8,@date)开始时间,
dateadd(ms,-3,dateadd(hour,20,@date))结束时间
union all
select '晚班' as msg,dateadd(hour,20,@date),
dateadd(ms,-3,dateadd(hour,8,dateadd(day,1,@date)))班次 开始时间 结束时间
---- ----------------------- -----------------------
早班 2009-06-01 08:00:00.000 2009-06-01 19:59:59.997
晚班 2009-06-01 20:00:00.000 2009-06-02 07:59:59.997(2 行受影响)
returns table
as
return(select '早班' as 班次,dateadd(hour,8,@date)开始时间,
dateadd(ms,-3,dateadd(hour,20,@date))结束时间
union all
select '晚班' as msg,dateadd(hour,20,@date),
dateadd(ms,-3,dateadd(hour,8,dateadd(day,1,@date)))
)
--调用
select * from time(getdate())
班次 开始时间 结束时间
---- ----------------------- -----------------------
早班 2009-06-01 08:00:00.000 2009-06-01 19:59:59.997
晚班 2009-06-01 20:00:00.000 2009-06-02 07:59:59.997