Sql Server

From this string
'D:\DBF_004_11_12_14_V11\FDMS\024.dbf' we need to extract the file name 024

Declare @fileName Varchar(500)set @fileName = 'D:\DBF_004_11_12_14_V11\FDMS\024.dbf'select LEFT(RIGHT(@fileName , charINDEX('\', reverse(@fileName )) - 1), 3)

Split string into a table using table valued functions

create Function dbo.fn_split(@Message Varchar(1000), @delimiter char(1), @index int ) Returns @TblSplit Table(Id Int Identity(1,1),Value
)Varchar(500)As
Begin
Declare
@curIndex
@pos
@prevPos
@result





int = 0, int = 1, int = 0, varchar(1000) while @pos > 0 Begin set @pos = CHARINDEX(@delimiter, @Message, @prevPos); if(@pos > 0) begin-- get the chars between the prev position to next delimiter pos


set @result = SUBSTRING(@message, @prevPos, @pos-@prevPos) end else begin--get last delim message


set @result = SUBSTRING(@message, @prevPos, LEN(@message)) end iNSERT INTO @TblSplit(Value) vALUES(@result)



set @prevPos = @pos + 1 set @curIndex = @curIndex + 1; end return; End

test:
select * FROM dbo.fn_SPLIT('D:\DBF_004_11_12_14_V11\FDMS\024.dbf','\', 2)

Get datetime in YYYYMM format–Sql Server

 

I need to get the date in YYYYMM format i.e. ‘2011-01-23’ becomes ‘201101’

declare @date date = '2016-11-23'
select  convert(varchar(6), @date, 112)

Sql Function to display Financial Quarter

Create Function fn_FinancialQuarter(@Date date )Returns Varchar(50)As
BeginDeclare @Quarter Varchar(50)Declare @Month Int = MOnth(@Date)
If(@Month <= 3)BeginSET @Quarter = 'Q-4 ' + Cast(YEAR(DATEADD(Year, -1, @date)) as varchar(10))EndElseBeginSET @Quarter = 'Q ' + Cast(((MONTH(@Date) - 4) / 3) + 1 as varchar(50)) + '-' + cast(YEAR(@date) as varchar(10))EndReturn @QuarterEnd