--try

select datepart(weekday,'2007-09-01')

------解决方案--------------------
declare @year as varchar(4)
declare @month as varchar(2)
declare @day as varchar(2)
set @year = '2007'
set @month = '09'
set @day = '30'
--组合成date类型的变量
select cast(@year + '-' + @month + '-' + day as datetime) riqi
--判断当前月的第一天是星期几
select datepart(week,cast(convert(varchar(7),getdate(),120) + '01' as datetime)