去空格 replace(ltrim(rtrim(phone)),' ','')
字符串轉化成整數 select MAX(convert(bigint,(replace(ltrim(rtrim([EmployeeNo])),' ',''))))
--sql Function 函數:
create function fn_IsWorkDay(@date datetime)
returns bit
as
BEGIN
declare @IsWorkDay bit
declare @WeekDayNum int
declare @Type varchar(10) --存放某一天是ON 還是OFF
set @IsWorkDay=0 --工作日,默認為False
set @date=CONVERT(varchar(12) , @date, 23 ) --'yyyy-MM-dd'
set @WeekDayNum=(select datepart(weekday,@date))
select @Type=(select [Type] from [HolidayDate] where CONVERT(varchar(10),[Date],120)=@date) --ON OFF
if(@WeekDayNum in (7,1)) --周末
begin
if(@Type='ON')
begin
set @IsWorkDay=1
end
end
else if(@WeekDayNum not in (7,1)) --周一到周五
begin
if(@Type='OFF')
begin
set @IsWorkDay=0
end
end
return @IsWorkDay
END
--定義遊標實現數據更新
declare @EmpNo varchar(10)
declare @DptCodeB varchar(12)
declare @DptCodeA varchar(12)
declare cur cursor
for select [EmployeeNo],[DepartCodeB] from [MainEmployee] where [DepartCodeB]<>'' and [DepartCodeB] is not null
open cur
fetch next from cur into @EmpNo,@DptCodeB
while(@@fetch_status=0)
begin
select @DptCodeA=(select [DepartCodeA] from [Plant_Dept] where [DepartCodeB]=@DptCodeB) --Select DeptCodeA
update [MainEmployee] set [DepartCodeA]=@DptCodeA where [EmployeeNo]=@EmpNo
fetch next from cur into @EmpNo,@DptCodeB
end
close cur
deallocate cur
