Convert CSV Values to INT

The given function converts the comma seperated values (CSV) to integer.


CREATE function [dbo].[CsvToInt] (@id varchar(5000))
returns @table table (wd_code int identity(1,1), [id] int)
as
begin
declare @separator char(1)
set @separator = ','
declare @separator_position int
declare @array_value varchar(5000)
set @id = @id + ','
while patindex('%,%' , @id) <> 0
begin
select @separator_position = patindex('%,%' , @id)
select @array_value = left(@id, @separator_position - 1)
if(@array_value = 'null')
insert @table ([id])
values (null)
else
insert @table ([id])
values (cast(@array_value as int))
select @id = stuff(@id, 1, @separator_position, '')
end
return
end

Check Locked Tables

Below is the query by which you can check if the table is locked or not


select distinct object_name(a.rsc_objid), a.req_spid, b.loginame
from master.dbo.syslockinfo a (nolock) join
master.dbo.sysprocesses b (nolock) on a.req_spid=b.spid
where object_name(a.rsc_objid) is not null
and a.rsc_objid = OBJECT_ID('TableName')

Get First and Last Date

I have created this query to get the first and last date of week,month,quater,half year nad year.


declare @TargetType table
(
TargetType_Code int
,Title varchar(225)
,FromDate datetime
,ToDate datetime
)


declare @TodayDate varchar(20),@TodayDayOfWeek int,@Month int,@Year int,@Quarter int


set @TodayDate = convert(varchar,getdate(),107)
set @TodayDayOfWeek = datepart(dw,getdate())
set @Month = month(getdate())
set @Year = year(getdate())
set @Quarter = datepart(quarter,getdate())


-- Set Daily Datetime
declare @DailyToDate datetime,@DailyFromDate datetime
set @DailyFromDate = cast(@TodayDate + ' 00:00:00' as Datetime)
set @DailyToDate = cast(@TodayDate + ' 23:59:59' as Datetime)
insert into @TargetType
values (1,'Today',@DailyFromDate,@DailyToDate)


-- Set Weekly Datetime
declare @WeeklyToDate datetime,@WeeklyFromDate datetime
set @WeeklyFromDate = dateadd(day,(@TodayDayOfWeek * -1) + 1,@DailyFromDate)
set @WeeklyToDate = cast(dateadd(day,6,@WeeklyFromDate) + ' 23:59:59' as Datetime)
insert into @TargetType
values (2,'This Week',@WeeklyFromDate,@WeeklyToDate)


-- Set Monthly DateTime
declare @MonthlyToDate datetime,@MonthlyFromDate datetime
set @MonthlyFromDate = cast( cast(@Year as varchar) + '-' + cast(@Month as varchar) + '-01 00:00:00' as datetime)
set @MonthlyToDate = cast(dateadd(day,-1,dateadd(month,1,@MonthlyFromDate)) + ' 23:59:59' as Datetime)
insert into @TargetType
values (3,'This Month',@MonthlyFromDate,@MonthlyToDate)


-- Set Quarter DateTime
declare @QuarterToDate datetime,@QuarterFromDate datetime
set @QuarterFromDate = cast( cast(@Year as varchar) + '-' + cast(((@Quarter - 1) * 3 + 1) as varchar) + '-01 00:00:00' as datetime)
set @QuarterToDate = cast(dateadd(day,-1,dateadd(month,3,@QuarterFromDate)) + ' 23:59:59' as Datetime)
insert into @TargetType
values (4,'This Quuater',@QuarterFromDate,@QuarterToDate)


-- Set HalfYearly DateTime
declare @HalfYearlyToDate datetime,@HalfYearlyFromDate datetime
set @HalfYearlyFromDate = cast( cast(@Year as varchar) + '-' + cast((((@Quarter /3) * 6) + 1) as varchar) + '-01 00:00:00' as datetime)
set @HalfYearlyToDate = cast(dateadd(day,-1,dateadd(month,6,@HalfYearlyFromDate)) + ' 23:59:59' as Datetime)
insert into @TargetType
values (5,'Half Yearly',@HalfYearlyFromDate,@HalfYearlyToDate)


-- Set Year DateTime
declare @YearToDate datetime,@YearFromDate datetime
set @YearFromDate = cast( cast(@Year as varchar) + '-01-01 00:00:00' as datetime)
set @YearToDate = cast(dateadd(day,-1,dateadd(year,1,@YearFromDate)) + ' 23:59:59' as Datetime)
insert into @TargetType
values (6,'This Year',@YearFromDate,@YearToDate)


select * From @TargetType