T-SQL equivalent for NEXT_DAY() of ORACLE


I thought of creating a simple function for this operation, in T-SQL.

Function Name: Next_Day

Input Params: @FromDate – Date from which you wish to get the next day; @DayNumber – Day of week for which you wish to get the next day (6=>Saturday, 7=>Sunday)

Operation: SELECT dbo.Next_Day(GETDATE(),6) //Returns the next Saturday (6) from today

Function: Function body

CREATE FUNCTION dbo.Next_Day
(
@FromDate DATETIME,
@DayNumber INT
)
RETURNS DATETIME
AS
BEGIN

DECLARE @CurrentDate AS DATETIME
DECLARE @DayOfWeek AS TINYINT

SELECT @DayOfWeek = DATEPART(WEEKDAY, @FromDate)
SELECT @DayNumber = @DayNumber + 1

IF (@DayOfWeek < @DayNumber) — Not equal to Thursday
BEGIN
SELECT @CurrentDate = (@FromDate + (@DayNumber-@DayOfWeek))
END
ELSE
BEGIN
SELECT @CurrentDate = (@FromDate + ((@DayNumber+2)-(@DayOfWeek-@DayNumber)))
END

RETURN @CurrentDate
END

GO

–SELECT dbo.Next_Day(‘2011-04-05’,6) //2011-04-09 11:46:38.867 – next saturday after 5th April 2011

Run the above SQL statement and you get your function.

Reference CodeLink

 

Convert a comma separted list to a table in SQL


I thought of sharing this simple function that you can use for splitting a list or nvarchar(max) or anything that is coma separated and sent to DB, into a temp table.

Reason:

Performance is a bottle neck for us. As for that matter, for any SP executions. We cannot live with the IN operator. It is always advicable to go ahead with SET based operations like JOINs in these cases. They also have a good track record of showing an improved performance to a greater extend.

If you need to have such a list in a table format, it is always advicable to have a generic function in your DB to handle such operations, as you might have such scenarios coming up in other SPs also.

Adding the SplitList Function to fulfil the above requirement is shared below:

CREATE FUNCTION [dbo].[SplitList]   
(   
 @List nvarchar(max),   
 @SplitOn nvarchar(5)   
)     
RETURNS @RtnValue table    
(   
     
 Id int identity(1,1),   
 Value nvarchar(100)   
)    
AS     
BEGIN   
   
While (Charindex(@SplitOn,@List)>0)   
Begin    
Insert Into @RtnValue (value)   
Select    
    Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))    
    Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))   
End    
   
Insert Into @RtnValue (Value)   
    Select Value = ltrim(rtrim(@List))   
    Return   
END 

–declare @tempList nvarchar(max)
–set @tempList = ‘1,2,3,4,5,6,7,8,9,10’

–select VALUE FROM dbo.SplitList(@tempList,’,’) — Remember, this will give you a table.. so store this statement into a temptable

 

Run the above function in your DB and you can start using the split list operation. Check the last 3 statements to see the usage of this function.

You normally should do

INSERT INTO @tbl_MyTempTable (ListValue)

SELECT VALUE FROM dbo.SplitList(@tempList,’,’) — my list is a coma separated one..

The above @tbl_MyTempTable will have my list (‘1,2,3,4,5’) stored as each rows..

Hope this was helpful…

Encrypting and Decrypting passwords in SQL server


We have come across scenarios wherein highly sensitive information like passwords being stored in our database. It is a highly likely chance that during SQL Injection or unprecedented attacks, these table values gets exposed. I will explain here a simple means of encrypting, followed by decrypting the same password for user authentication. But let me remind you, that cracking this logic is easy. Also, if you are smart, you can write your own procedures to find the password from an encrypted on.

To Encypt a password, all you need to do is use the pwdencrypt(‘password’) method.

Check the example below:
Declare @x varbinary(255)
SET @x = pwdencrypt(‘AAAA’)
print @x

The output of the above query would be(will vary with you unique machine id):
0x010036D726AE86834E97F20B198ACD219D60B446AC5E48C54F30

However the decrypt is not as simple as encrypt. In fact there is no direct method to decrypt the password. Instead SQL Server provide a method to compare the password you passed with the encrypted password, using the pwdcompare(‘password passed’,encrypted password).

Check the example below:

select pwdcompare(‘AAAA’,0x01008C75C2236E7101F87898988A5AEEE995D4CDFD16A8C95ECD)

This query will return 1 or 0 depending on the equality of the password.