SQL - Get Fiscal Year From Date

In SQL Reporting often we have the scenario where we need to do the reporting based on financial  year. Fiscal year may vary from one organization to another, so feel free to update the function, Currently it set to Australian Financial Year.

 SQL Function:

CREATE FUNCTION [dbo].[fnGetFiscalYear](
@CalenderDate DATETIME)
RETURNS NVARCHAR(10)
AS
BEGIN

DECLARE @FYYear INT
DECLARE @ResultVar NVARCHAR(10)

SET @ResultVar = ''

SELECT @FYYear = CASE WHEN MONTH(@CalenderDate) > 6 --New Fiscal Year stars on the fist day of July
THEN YEAR(@CalenderDate) + 1
ELSE YEAR(@CalenderDate)
END

SELECT @ResultVar  = 'FY-' + RIGHT(@FYYear,2)

RETURN @ResultVar
END


Example:

SELECT GETDATE(), DBO.fnGetFiscalYear(GETDATE())


Result:

2014-11-28 13:52:21.603 FY-15

How to Remove Special Characters Using SQL

Remove Special Characters Using TSQL


CREATE FUNCTION [dbo].[RemoveSpecialCharacters](@StrValue NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN

    Declare @KeepValues as varchar(50)

SET @StrValue = LTRIM(RTRIM(@StrValue))

    SET @KeepValues = '%[^a-z0-9]%'

    WHILE PATINDEX(@KeepValues, @StrValue) > 0
        SET @StrValue = STUFF(@StrValue, PATINDEX(@KeepValues, @StrValue), 1, '')

    Return @StrValue
END

Result:

SELECT DBO.RemoveSpecialCharacters('Welcome %To $Axil - 007')

WelcomeToAxil007

Remove / Trim Leading Zeros - SQL Server

TSQL - Removing leading zeros from varchar value:

How to remove leading zeros from a number using TSQL

ALTER FUNCTION [dbo].[RemoveLeadingZero] (@StrValue NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
SET @StrValue = ISNULL(@StrValue,'')

SET @StrValue =  SUBSTRING(@StrValue, PATINDEX('%[^0]%', @StrValue+'.'), LEN(@StrValue))
   
Return @StrValue
END

Result:

SELECT DBO.RemoveLeadingZero('00050407')

50407

SELECT DBO.RemoveLeadingZero('00050470')

50470

SELECT DBO.RemoveLeadingZero('000.5430')

.5430

SQL - Get Date From Week Number & Year

Convert week number back into the date range for that week using T-SQL

DECLARE @Week INT
DECLARE @Year NVARCHAR(4);

SELECT @Year = 2014, @Week = 40

SELECT DATEADD(WEEK, DATEDIFF(WEEK, 6, '1/1/' + @Year) + (@Week-1), 6) AS StartOfWeek;
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 5, '1/1/' + @Year) + (@Week-1), 5) AS EndOfWeek;

How to sort the index numbers using SQL

How to sort a page or chapter index numbers stored in a varchar fileld?

1.3.1
1.3.2
1.3.3
1.1.3
1.10
1.10.1
1.10.2
1.10.3
1.2
1.2.1
1.2.10
1.2.11

Here is the solution for this...

-- Sample table with page or chapeter index to sort

SELECT '1.1.0' PageIndex INTO #MY_TESTUNION SELECT '1.3.1' UNION SELECT '1.3.2' UNION SELECT '1.3.3' UNION SELECT '1.1.3' UNION SELECT '1.10' UNION SELECT '1.10.1' UNION SELECT '1.10.2' UNION SELECT '1.10.3' UNION SELECT '1.2' UNION SELECT '3.10.1'UNION SELECT '3.9.1'UNION SELECT '11.1.0'

-- UD Function to sort the index numbers create function dbo.SortNumber(@indexnum varchar(50))returns varchar(50)as
begindeclare @curlength int, @length intdeclare @rtnstring varchar(50)select @rtnstring = '', @curlength = 0while (@curlength < len(@indexnum)) -- loop until reach the last "." markbeginset @length = charindex('.', @indexnum, @curlength + 1)if @length = 0set @length = len(@indexnum) + 1-- pading with leading zeroset @rtnstring = @rtnstring + right('0000' + substring(@indexnum, @curlength + 1, @length - @curlength - 1), 4) + '.'set @curlength = @lengthendreturn @rtnstringend
--sample output of this function
SELECT dbo.SortNumber('1.10.1') = 0001.0010.0001.SELECT dbo.SortNumber('1.1.2') = 0001.0001.0002.  

-- final result
SELECT PageIndex,dbo.SortNumber(PageIndex) SortedIndex FROM #MY_TEST ORDER BY SortedIndex