SQL SERVER – List the Name of the Months Between Date Ranges – Correction

Earlier I wrote a blog post about SQL SERVER – List the Name of the Months Between Date Ranges. It was written in the response to the question – How to list the name of the months between two date ranges?

When I wrote the blog post, there was a small error on my part in the script where I forgot to include the beginning month in the answer of the question. SQL Server Expert Sanjay Monpara caught this error and he immediately wrote a comment on the blog post with a correction. Sanjay has previously contributed to the blog with his expertise and is well known to the readers.

He modified my script to correct the error. Here is the script which generates names of the months between two dates.

DECLARE @StartDate  DATETIME,
@EndDate    DATETIME;
SELECT @StartDate = '20140301' -- March
,@EndDate   = '20140901'; -- September
SELECT  DATENAME(MONTH, DATEADD(MONTH, nos.monthnos, @StartDate)-1) AS MonthName
FROM    (SELECT 1 monthnos UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) nos
WHERE     nos.monthnos <= DATEDIFF(MONTH, @StartDate, @EndDate)+1;

Above script does return the name of the months between date ranges. There are few more great suggestions in the comments of the blog post, I encourage you to check them out.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

SQL SERVER – Reverse String Word By Word – Part 4

Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara and Yuri Petrov contributed to the blog post with their excellent script which I have blogged earlier. In this blog post we will see a similar script by SQL Expert Paul G. He has essentially sliced the string up by the passed delimiter (in this case space) and reconstructed the string the reverse order.

Here is the script by Paul:

DECLARE @String NVARCHAR(50)
DECLARE @Delimiter CHAR(1)
DECLARE @count INT
DECLARE
@slice VARCHAR(8000)
DECLARE @rtnString NVARCHAR(50)
SET @Delimiter = ' '
SET @String = 'I am Pinal Dave'
SET @rtnString = ''
SET @count = 1
WHILE @count > 0
BEGIN
SET
@count = CHARINDEX(@Delimiter,@String)
IF (@count > 0)
SET @slice = LEFT(@String,@count - 1)
ELSE
SET
@slice = @String
IF(LEN(@slice) > 0)
SET @rtnString = @slice + ' ' + @rtnString
SET @String = RIGHT(@String,LEN(@String) - @count)
END
SELECT
@rtnString

The above query will return results in the reverse order.

For example, as we have declared the variable as ‘My Name is Pinal Dave’, it will return results as ‘Dave Pinal is Name My’.

Thanks Paul for awesome contribution.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Reverse String Word By Word – Part 3

Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara wrote a great comment to the blog post where he has proposed alternate solution. Right after the solution of the Sanjay, another expert Yuri Petrov suggested a modification to the script. In this blog post we will be seeing the solution proposed by Yuri Petrov in this blog post.

Yuri’s script is in two parts.

Part 1: Create a auxiliary table with numbers.

CREATE TABLE Nums (num INT PRIMARY KEY);
WITH
cteNums8000
AS (
SELECT TOP (8000) ROW_NUMBER () OVER (ORDER BY (SELECT 0)) AS [num]
FROM sys.columns a
CROSS JOIN sys.columns b
)
INSERT INTO Nums
SELECT num FROM cteNums8000;
GO

Part 2: Script to Reverse String Words. If you want, you can convert this script to function afterwards.

DECLARE @ip VARCHAR (8000) = 'My Name is Pinal Dave';
WITH
cteSplit
AS (
SELECT SUBSTRING (@ip, num, CHARINDEX (' ', @ip + ' ', num) - num) AS [value], num
FROM Nums
WHERE num <= LEN (@ip)
AND
SUBSTRING (' ' + @ip, num, 1) = ' '
)
SELECT STUFF ((SELECT ' ' + value
FROM cteSplit
ORDER BY num DESC
FOR XML
path (''), TYPE).value ('.', 'varchar (8000)')
,
1, 1, '')

The above query will return results in the reverse order.

For example, as we have declared the variable as ‘My Name is Pinal Dave’, it will return results as ‘Dave Pinal is Name My’.

Thanks Yuri for awesome contribution.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Reverse String Word By Word – Part 2

Earlier I wrote a blog post about SQL SERVER – Reverse String Word By Word. SQL Server Expert Sanjay Monpara wrote a great comment to the blog post where he has proposed alternate solution. Right after the solution of the Sanjay, another expert Yuri Petrov suggested a modification to the script. I have taken in consideration both the comments and solution and build a more robust script over here.

Here is the script to create a function

CREATE FUNCTION [dbo].[Split] (@string AS VARCHAR(8000), @splitAt AS VARCHAR(8000))
RETURNS @strings TABLE (ID INT, Strings VARCHAR(8000))
AS
BEGIN
DECLARE
@splitLen AS INT
DECLARE
@index AS INT
DECLARE
@id AS INT
SET
@id = 0
SET @splitLen = LEN(@splitAt)
SET @splitAt = '%' + @splitAt + '%'
SET @index = PATINDEX(@splitAt, @string)
WHILE(@index > 0)
BEGIN
SET
@id = @id+1
INSERT INTO @strings VALUES(@id,SUBSTRING(@string, 1, @index-1))
SET @string = SUBSTRING(@string, @index + 1, LEN(@string))
SET @index = PATINDEX(@splitAt, @string)
END
IF
LEN(@string) > 0
BEGIN
SET
@id = @id+1
INSERT INTO @strings VALUES(@id, @string)
END
RETURN
END

Here is how you will use the function:

DECLARE @Input NVARCHAR(MAX)
SET @Input = 'This is Reverse String test'
DECLARE @Output NVARCHAR(MAX)
SET @Output = ''
SELECT @Output = strings + ' ' + @Output FROM dbo.split(@Input,' ')
ORDER BY ID
PRINT @Output

The above script will return results just as we have seen earlier blog post.

 

Thanks Sanjay for awesome contribution.

In addition to the above Yuri Petrov has also suggested another script which we will see in future blog posts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Fixing Backup Error – Operating system error 5(Access is denied.) – SQL in Sixty Seconds #077

This error has to be one of the most famous error. I have installed SQL Server quite a many times, but I keep on getting this error once in a while. This is also one of the most searched error online. I have previously blogged about this but today, I planned to create a small video on the same subject. In this video I demonstrate how one can resolve the permissions issue with this error.

ere are few other blog posts related to this error, which I have written earlier.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Reverse String Word By Word

Earlier I wrote a blog post where I have reversed the entire string which was passed to the function. You can read that over here User Defined Functions (UDF) to Reverse String. In that blog post I wrote a function which would reverse an entire string. However, recently I read a question in SQLBangalore where the user wanted to reverse string, but wanted to keep all the words in the same order. For example,

If the string is – “I am Pinal Dave”, it should be reversed as “Dave Pinal am I.”

Well, here is the function which does the same task.

CREATE FUNCTION [dbo].[fn_ReverseWordsInSentence]
(
@ip VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE
@op VARCHAR(MAX)
SET  @op = ''
DECLARE @Lenght INT
WHILE
LEN(@ip) > 0
BEGIN
IF CHARINDEX
(' ', @ip) > 0
BEGIN
SET
@op = SUBSTRING(@ip,0,CHARINDEX(' ', @ip)) + ' ' + @op
SET @ip = LTRIM(RTRIM(SUBSTRING(@ip,CHARINDEX(' ', @ip) + 1,LEN(@ip))))
END
ELSE
BEGIN
SET
@op = @ip + ' ' + @op
SET @ip = ''
END
END
RETURN
@op
END
-- Usage
SELECT  [dbo].[fn_ReverseWordsInSentence] ('My Name is Pinal Dave')
/*
Dave Pinal is Name My
*/

Let me know if you have a better way to solve this problem.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Puzzle – ISNUMERIC and Unexpected Results – SQL in Sixty Seconds #076

It has been a long time since I have asked, puzzled on this blog so let us have fun time together with ISNUMERIC function. If you get the correct answer to this question, I will give you one month free subscription to Pluralsight.

The question is in the form of video which is displayed here. Watch the video and answer correct question in the comment on the this blog. You have 48 hours from the time of publishing this blog to answer it correctly.

If you due to any reason, you can’t watch the video, here is the image taken from the video. I strongly suggest you watch this video to get a clear idea of the puzzle.

In the above image, when I execute first select statement, it returns me answer as 0 but when I execute the second function, it gives me answer as 1. What is passed in the second select statement in ISNUMERIC function which is producing answer as 1, even though it is not visible.

Please leave your answer as a comment to this blog post.

Reference: Pinal Dave (http://blog.sqlauthority.com)