SQL Server学习记录之convert和cast的用法

convert是日期格式化函数

cast是将目的数据类型转化为基本数据类型

在工作中有这么个需求，星期一到星期五开三个房间，星期六到星期日再增加两个房间，月末再增加一个房间

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
go
--创建定时赛自动开启作业
create procedure ContestTask
as
begin
set nocount on
declare @_weekday int --星期几
declare @_currentdate datetime--当前时间
declare @_currentYMD varchar(20)--当前年月日
declare @_room_char varchar(50)
declare @_room_datatime datetime--房间时间
declare @_month_last datetime--本月的最后一天
set @_currentdate=getdate()--获得当前时间
set @_weekday=datepart(dw,@_currentdate) 
select @_currentYMD=CONVERT(varchar(20),@_currentdate, 23)--获得年月日
select @_month_last=DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,@_currentdate),120)+'1')--获取本月最后一天
if @_weekday>=2 and @_weekday<=6--周一到周五(开三个房间)
begin
	set @_room_char=@_currentYMD+' 18:00:00'--设置房间1时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=1
	set @_room_char=@_currentYMD+' 19:00:00'--设置房间2时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=2
	set @_room_char=@_currentYMD+' 20:00:00'--设置房间3时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=3
end
if @_weekday=1 or @_weekday=7--周六，周日(开五个房间)
begin
	set @_room_char=@_currentYMD+' 18:00:00'--设置房间1时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=1
	set @_room_char=@_currentYMD+' 19:00:00'--设置房间2时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=2
	set @_room_char=@_currentYMD+' 20:00:00'--设置房间3时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=3
	set @_room_char=@_currentYMD+' 16:00:00'--设置房间4时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=4
	set @_room_char=@_currentYMD+' 17:00:00'--设置房间5时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=5
end
if DATEDIFF(d,@_currentdate,@_month_last)=0--月末(开六个房间)
begin
	set @_room_char=@_currentYMD+' 18:00:00'--设置房间1时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=1
	set @_room_char=@_currentYMD+' 19:00:00'--设置房间2时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=2
	set @_room_char=@_currentYMD+' 20:00:00'--设置房间3时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=3
	set @_room_char=@_currentYMD+' 16:00:00'--设置房间4时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=4
	set @_room_char=@_currentYMD+' 17:00:00'--设置房间5时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=5
	set @_room_char=@_currentYMD+' 20:00:00'--设置房间6时间
	select @_room_datatime=CAST(@_room_char as datetime)
	update TContestInfo set StartTime=@_room_datatime where ContestID=6
end
end