Feeds:
Posts
Comments

Archive for May, 2013

SQL SERVER – Convert short month name to month name

Posted in Solutions, SQL SERVER, tagged , , , , on May 31, 2013| 2 Comments »

Few days ago, I was working on a report and came across with an issue regarding conversion of Short month name to Month name and we do not have any built-in function to do it in SQL Server. So I commenced my research and finally found few solutions.

Before proceeding with the solutions, let me create a sample to demonstrate this solution.

USE tempdb
GO
CREATE TABLE tbl_MonthName
(
[ID] INT,
[SHORT_MONTH_NAME] VARCHAR(3)
)
GO

INSERT INTO tbl_MonthName
SELECT 1, N'Jan' UNION ALL
SELECT 2, N'Feb' UNION ALL
SELECT 3, N'Mar' UNION ALL
SELECT 4, N'Apr' UNION ALL
SELECT 5, N'May' UNION ALL
SELECT 6, N'Jun' UNION ALL
SELECT 7, N'Jul' UNION ALL
SELECT 8, N'Aug' UNION ALL
SELECT 9, N'Sep' UNION ALL
SELECT 10, N'Oct' UNION ALL
SELECT 11, N'Nov' UNION ALL
SELECT 12, N'Dec'
GO

SELECT * FROM tbl_MonthName
GO
--OUTPUT

MonthShortName1.1

Method 1 :
In this method, we will use the old approach. In this approach, we need to manually concatenate day with short month name and year to make a complete date of that particular month using additional single quotes and then get the Month name from this concatenate date using DATENAME function. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
[ID]
,[SHORT_MONTH_NAME] AS [SHORT MONTH NAME]
,DATENAME(MONTH,CONVERT(
DATETIME,'1'+ [SHORT_MONTH_NAME] + '1')) AS [MONTH NAME]
FROM tbl_MonthName
GO
--OUTPUT

MonthShortName1.2

Method 2 :
In this method, we will use the New approach. In this approach, we need to automatically concatenate day with short month name and year to make a complete date of that particular month using CONCAT function (without any single quotes) and then get the Month Name using FORMAT function. Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
[ID]
,[SHORT_MONTH_NAME] AS [SHORT MONTH NAME]
,FORMAT(CONVERT(
DATETIME,CONCAT(1,[SHORT_MONTH_NAME],1)),'MMMM') AS [MONTH NAME]
FROM tbl_MonthName
GO
--OUTPUT

MonthShortName1.2

Conclusion :
In both methods, we used different approaches using manual concatenation and automatic concatenation using CONCAT function. I would recommend Method 2 because in my earlier article I found that  CONCAT function is much faster than the normal concatenations.

Read Full Post »

SQL SERVER – Passing multiple values through one parameter in a stored procedure

Posted in Solutions, SQL SERVER, tagged , , , , on May 31, 2013| 16 Comments »

In my earlier article, I developed a solution How to use multiple values for IN clause using same parameter (SQL Server). Recently, I received a query, inquiring how to pass multiple values through one parameter in a stored procedure.

Given below are the two different Methods :

Method 1 : Using XQuery

In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to convert the string parameter to xml variable and split it using comma delimiter and then you can query it.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
CREATE PROCEDURE usp_Employeelist
@Cities NVARCHAR(30)
AS
DECLARE @CitiesXML AS XML
SET @CitiesXML = cast(('<a>'+replace(@Cities,',' ,'</a><a>')
+'</a>') AS XML)

SELECT
BusinessEntityID
, FirstName
, JobTitle
, City
FROM HumanResources.vEmployee
WHERE City IN
(
SELECT
A.value('.', 'varchar(max)')
FROM @CitiesXML.nodes('A') AS FN(A)
)
ORDER BY BusinessEntityID
GO

--Execute the stored procedure using multiple values
--through one parameter in this stored procedure
USE AdventureWorks2012
GO
EXEC usp_Employeelist
'Cambridge,Newport Hills,Berlin,Bordeaux'
GO
--OUTPUT

Passingmultiple1.1

Method 2 : Using Dynamic queryhe 

In this solution, you need to pass a single comma delimiter string to the stored procedure. Once it is passed, you need to replace the parameter with single quote and create a dynamic query and execute it.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE AdventureWorks2012
GO
CREATE PROCEDURE usp_Employeelist
@Cities NVARCHAR(30)
AS

DECLARE @Sql VARCHAR(MAX)
SET @Cities = REPLACE(@Cities,',',''',''')

SET @Sql = 'SELECT
BusinessEntityID
, FirstName
, JobTitle
, City
FROM HumanResources.vEmployee
WHERE City IN
(
''' + @Cities + '''
)
ORDER BY BusinessEntityID'

EXEC (@Sql)
GO

--Execute the stored procedure using multiple values
--through one parameter in this stored procedure
USE AdventureWorks2012
GO
EXEC usp_Employeelist
'Cambridge,Newport Hills,Berlin,Bordeaux'
GO
--OUTPUT

Passingmultiple1.1

Read Full Post »

SQL SERVER – Multiple ways to remove Milliseconds from Datetime (Truncate Datetime till Second)

Posted in Solutions, SQL SERVER, tagged , , , , on May 30, 2013| 5 Comments »

I came across this problem recently, when I was working on a report and I needed to truncate the date time till seconds. Given below are the two different methods we can use to achieve it.

Method 1 :
In this method, we will first convert the datetime value to varchar, truncate it and then convert it back to datetime data type using CONVERT function twice.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with millisconds]
,CONVERT(DATETIME,CONVERT(VARCHAR(20),@datetime,120))
AS [Datetime without millisconds]
GO
--OUTPUT

truncatemilliseconds

Method 2 :
In this method, we will first FORMAT the datetime value to a defined format (truncate milliseconds) and then convert it back to datetime data type using CAST function.

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT
@datetime AS [Datetime with millisconds]
,CAST(FORMAT(@datetime,'yyyy-MM-dd HH:mm:ss') AS datetime)
AS [Datetime without millisconds]
GO
--OUTPUT

truncatemilliseconds1.1

Read Full Post »

SQL SERVER – Fix – Error :156 – Incorrect syntax near the keyword ‘UNION’.

Posted in Errors, SQL SERVER, tagged , , , , on May 28, 2013| Leave a Comment »

UNION operator is one of the most frequent operators used in SQL Server scripting. As per MSDN  it is used to combine the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union . But there is an issue with Union operator which prohibits order by for each table separately while using UNION / UNION ALL. It generates an error.

Let me explain this error in detail :

Message Number: 156

Severity : 15

Error Message: Incorrect syntax near the keyword ‘UNION’.

Error Generation:

Let me create a sample to demonstrate this error.

USE tempdb
GO

CREATE TABLE tbl_Sample1
(
 [ID] INT,
 [Grade] VARCHAR(50)
)
GO
CREATE TABLE tbl_Sample2
(
 [ID] INT,
 [Grade] VARCHAR(50)
)

INSERT INTO tbl_Sample1 VALUES (1,'Grade A')
INSERT INTO tbl_Sample1 VALUES (2,'Grade B')
INSERT INTO tbl_Sample1 VALUES (3,'Grade C')
INSERT INTO tbl_Sample1 VALUES (4,'Grade D')
INSERT INTO tbl_Sample1 VALUES (5,'Grade E')
INSERT INTO tbl_Sample1 VALUES (6,'Grade F')
GO
INSERT INTO tbl_Sample2 VALUES (1,'1st')
INSERT INTO tbl_Sample2 VALUES (2,'2nd')
INSERT INTO tbl_Sample2 VALUES (3,'3rd')
INSERT INTO tbl_Sample2 VALUES (4,'4th')
INSERT INTO tbl_Sample2 VALUES (5,'5th')

Once you created the above sample, lets make a UNION ALL query and try to ORDER BY each table separately.

USE tempdb
GO
SELECT [ID],[Grade]
FROM tbl_Sample1
Order By [ID]

UNION ALL

SELECT [ID],[Grade]
FROM tbl_Sample2
Order By [ID]

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword ‘UNION’.

Ooopps…… I am unable to execute it.

Resolution:
It is very simple to resolve, just add one more column with any sorting number… either 1,2,3 or A,B,C and order on the basis of this column. Let me demonstrate it.

USE tempdb
GO
SELECT
		  'A' AS [Order ID]
		, [ID]
		, [Grade]
FROM tbl_Sample1

UNION ALL

SELECT
		  'B' AS [Order ID]
		, [ID]
		, [Grade]
FROM tbl_Sample2
Order By [Order ID]
--OUTPUT

errormsg156.1.1
Conclusion :

Remember, whenever you use UNION / UNION ALL and have to order by each table separately, just add one more column with any sorting value and order by on the basis of this column.

Read Full Post »

SQL SERVER 2012 – Differences between RAISERROR and THROW with examples

Posted in Errors, SQL SERVER, tagged , , , , on May 27, 2013| 2 Comments »

A few months ago, I wrote an article about THROW a new technique to raise an exception and transfer execution to a CATCH block shipped with SQL Server 2012. I also compared throw with Raiserror in that article partially. Later on I found a lot of differences and found that THROW is much better than RAISERROR. Let me create a sample error message to demonstrate the difference.

EXEC sys.sp_addmessage
@msgnum   = 80000
,@severity = 10
,@msgtext  = N'This is a test message.'
,@lang = 'us_english';
GO

Let me explain the differences:

No

RAISERROR

THROW

1

 If you pass any message_id to RAISERROR, the message_id must be available in sys.messages.

Example :

RAISERROR (51000, -- Message id.
10, -- Severity,
1, -- State,
N'This is a test message');

–OUTPUT
Msg 18054, Level 16, State 1, Line 1
Error 51000, severity 10, state 1 was raised, but no message
with that error number was found in sys.messages.
If error is larger than 50000, make sure the user-defined
message is added using sp_addmessage.

 If you pass any message_id to THROW, the message_id is not necessary to be available in sys.messages.
Example :

THROW 51000
, 'This is a test message'
, 1
;

–OUTPUT
Msg 51000, Level 16, State 1, Line 1
This is a test message

2

 If you pass any message_id to RAISERROR, the message_id range must be between 13000 and 2147483647 and it cannot be 50000.
Example :

RAISERROR (49913, -- Message id.
10, -- Severity,
1, -- State,
N'This is a test message');

–OUTPUT
The server could not load DCOM. Software Usage Metrics cannot be started without DCOM.

 If you pass any message_id to THROW, the message_id range must be between 50000 and 2147483647.
Example :

THROW 49913
, N'This is a test message'
, 1
;

–OUTPUT
Msg 35100, Level 16
State 10, Line 1
Error number 49913 in
the THROW statement
is outside the valid range.
Specify an error number
in the valid range of
50000 to 2147483647.

3

 If you pass any existing message_id to RAISERROR, it will act upon the same Severity level.

Example :

RAISERROR (80000, -- Message id.
10, -- Severity,
1, -- State,
N'This is a test message');

–OUTPUT
This is a test message.

 If you pass any message_id to THROW, whatever the Severity level is, it will always show Severity level 16.
Example :

THROW 80000
, N'This is a test message'
, 1
;

–OUTPUT
Msg 80000, Level 16
State 16, Line 1
This is a test message

4

 If you use RAISERROR to trap the bug, it will raise the error but with the incorrect error number.
Example :

BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 1 / 0
END TRY
BEGIN CATCH

DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (
@ErrorMessage, -- Message.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;

–OUTPUT
Msg 50000, Level 16
State 1, Line 15
Divide by zero error encountered.

 If you try to use THROW to raise the error, it will give you the exact error number.
Example :

BEGIN TRY
DECLARE @VALUE INT
SET @VALUE = 1 / 0
END TRY

BEGIN CATCH
THROW;
END
CATCH

;

–OUTPUT
Msg 8134, Level 16
State 16, Line 4
Divide by zero error encountered.

5

 RAISERROR can contain printf formatting styles
Example :

RAISERROR (
N'@@@%*.*s@@@',
10,
1,
0,
30,
N'This is a test message
For SQL Server 2012');
GO

–OUTPUT
@@@This is a test message For SQL@@@

 THROW cannot contain printf formatting styles.
Example :

THROW 80000
,N'@@@%*.*s
@@@This is a test message'
, 1

;

–OUTPUT
Msg 80000, Level 16
State 1, Line 1

Conclusion :
As per the above differences THROW function leads over RAISERROR and in my opinion also THROW is better to raise an exception and transfer execution to a CATCH block but again it depends upon the requirements.

Read Full Post »

SQL SERVER – Faster way to calculate running total

Posted in Solutions, SQL SERVER, tagged , , , on May 25, 2013| 2 Comments »

A couple of days ago, I developed a tool how to recover the dropped index without backup. In this tool, running total needs to be generated. So I did some research and came across couple of options along with the best option as well.

Given below are the three methods :

Method 1:
In this method, we will use the self join to calculate running total.

--This script will work on SQL Server 2005 and above.
USE AdventureWorks2012
GO
SELECT
A.SalesOrderID AS [SalesOrderID]
, A.SalesOrderDetailID AS [SalesOrderDetailID]
, A.ProductID AS [ProductID]
, A.LineTotal AS [LineTotal]
, SUM(B.lineTotal) AS [Running Total]

FROM SALES.SalesOrderDetail A
INNER JOIN Sales.SalesOrderDetail B
ON A.SalesOrderID =B.SalesOrderID
AND B.SalesOrderDetailID<=A.SalesOrderDetailID
WHERE A.SalesOrderID =43659
GROUP BY
A.SalesOrderID
, A.SalesOrderDetailID
, A.ProductID
, A.LineTotal
ORDER BY
A.SalesOrderID
, A.SalesOrderDetailID
, A.ProductID
, A.LineTotal
GO

Method 2:
In this method, we will use the sub query to calculate running total.

--This script will work on SQL Server 2005 and above
USE AdventureWorks2012
GO
SELECT
A.SalesOrderID AS [SalesOrderID]
, A.SalesOrderDetailID AS [SalesOrderDetailID]
, A.ProductID AS [ProductID]
, A.LineTotal AS [LineTotal]
, (
SELECT SUM(B.lineTotal)
FROM SALES.SalesOrderDetail B
WHERE A.SalesOrderID =B.SalesOrderID
AND B.SalesOrderDetailID<=A.SalesOrderDetailID
) AS [Running Total]
FROM SALES.SalesOrderDetail A
WHERE A.SalesOrderID =43659
GO

Method 3:
In this method, we will use the SUM function to calculate running total.

--This script will work on SQL Server 2008 R2 and above.
USE AdventureWorks2012
GO
SELECT
SalesOrderID
, SalesOrderDetailID
, ProductID
, LineTotal
, SUM(LineTotal)
OVER(PARTITION BY SalesOrderID
ORDER BY SalesOrderID,SalesOrderDetailID)
AS [Running Total]
FROM SALES.SalesOrderDetail
WHERE SalesOrderID =43659
GO

All of the above queries will give you the same result set. However lets view their performance .
runningtotal.1.1

Conclusion :
As you can see, the performance of Method 3 is better than other methods. Also, if you look at the line of code, it is also shorter in Method 3 vis-à-vis other methods. So, I would recommend method 3.

Read Full Post »

SQL SERVER – Resetting sequence values for entire database

Posted in Solutions, SQL SERVER, tagged , , , , , , , on May 24, 2013| Leave a Comment »

It is a common practice that whenever you need to deploy a production database, you need to first delete all the test data and reset almost all the identity values in the entire database. In SQL Server 2012 also, you need to do the same but in addition to this you need to reset the SEQUENCE object as well (if utilized).
Lets do it step by step.

Step 1 :
First of all, you need to check whether the SEQUENCE object exits in your database or not and what its current value is.
Given below is the script.

USE AdventureWorks2012
GO
SELECT
name AS [Sequence Name]
,SCHEMA_NAME(schema_id) AS [Scheme Name]
,[start_value] AS [Start Value]
,[current_value] AS [Current Value]
FROM sys.sequences
GO
--OUTPUT

resetsequence1.2

Step 2 :
As you can see, in the above list of sequences, the current value of sequences is not equal to the start value. Lets create the script to reset the sequences.

USE AdventureWorks2012
GO
SELECT
'ALTER SEQUENCE '
+  QUOTENAME(schema_name(schema_id))
+  '.'
+  QUOTENAME(name)
+  ' RESTART WITH '
+  TRY_CONVERT(nvarchar(50),[start_value])
AS [QUERY]
FROM sys.sequences
GO
--OUTPUT

resetsequence1.1

Step 3 :
Once you executed the above query, just copy the result set of the above query and paste it on to a new query window. Make sure you are in a correct database and then execute the result set of above query.

USE AdventureWorks2012
GO
ALTER SEQUENCE [dbo].[Ticket] RESTART WITH 1
ALTER SEQUENCE [dbo].[PurchaseOrderID] RESTART WITH 1
ALTER SEQUENCE [dbo].[SalesOrderID] RESTART WITH 1
ALTER SEQUENCE [dbo].[EmployeeID] RESTART WITH 1
ALTER SEQUENCE [dbo].[ContactID] RESTART WITH 1
GO
--OUTPUT

resetsequence1.3

Step 4 :
Once you execute the above reset statements in a new query window, execute the STEP 1 query again to check whether sequences are reset or not.

USE AdventureWorks2012
GO
SELECT
name AS [Sequence Name]
,SCHEMA_NAME(schema_id) AS [Scheme Name]
,[start_value] AS [Start Value]
,[current_value] AS [Current Value]
FROM sys.sequences
GO
--OUTPUT

resetsequence1.4

Now, you can see that all the sequence objects have been reset.

Read Full Post »

SQL SERVER – How to get short month name / weekday name from datetime

Posted in Solutions, SQL SERVER, tagged , , , , , on May 23, 2013| Leave a Comment »

I came across this question on multiple forums and it is being solved using multiple function such as DATENAME and LEFT function functions. However, lets solve it using FORMAT function only.
Let me create an example to explain it.

USE tempdb
GO
CREATE TABLE tbl_Sample
(
[ID] INT,
[Date] DATETIME
)
GO
INSERT INTO tbl_Sample VALUES (1,'2013-01-01')
INSERT INTO tbl_Sample VALUES (2,'2013-02-02')
INSERT INTO tbl_Sample VALUES (3,'2013-03-03')
INSERT INTO tbl_Sample VALUES (4,'2013-04-04')
INSERT INTO tbl_Sample VALUES (5,'2013-05-05')
INSERT INTO tbl_Sample VALUES (6,'2013-06-06')
INSERT INTO tbl_Sample VALUES (7,'2013-07-07')

Method 1 :
In this method, we will use DATENAME & LEFT function to achieve short name of month and weekday.
Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
SELECT
[ID]
, [Date]
, LEFT(DATENAME(MONTH,[Date]),3) AS [Short Month Name]
, LEFT(DATENAME(WEEKDAY,[Date]),3) AS [Short Weekday Name]
FROM tbl_Sample
--OUTPUT

MonthAndWeekdayshortname1.1

Method 2 :
In this method, we will use ONLY FORMAT function (shipped in SQL Server 2012) to achieve short name of month and weekday.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
USE tempdb
GO
SELECT
[ID]
, [Date]
, FORMAT([Date],'MMM') AS [Short Month Name]
, FORMAT([Date],'ddd') AS [Short Weekday Name]
FROM tbl_Sample
--OUTPUT

MonthAndWeekdayshortname1.2

Read Full Post »

SQL SERVER 2012 – Fix – Error :11508 – The undeclared parameter is used more than once in the batch being analyzed.

Posted in Errors, SQL SERVER, tagged , , , , on May 22, 2013| 1 Comment »

In my earlier article, I wrote about one of the new system stored procedure sp_describe_undeclared_parameters shipped with SQL Server 2012. This is very handy system stored procedure whenever you need to find undeclared parameters in a SQL query.
I was trying to find undeclared parameters in a query using this system stored procedure and came across with a new error message. I did some research and fixed it.

Let me explain this error in detail :

Message Number: 11508

Severity : 16

Error Message: The undeclared parameter ‘%.*ls’ is used more than once in the batch being analyzed.

Error Generation:

In the given below example, I need to search country region name and a portion of a name in First name, Middle Name & Last Name columns of the employee view. So I wrote a query and it works perfectly. Given below is the script.

USE [AdventureWorks2012]
GO
DECLARE @Search_Name AS VARCHAR(MAX)
DECLARE @Country_Region_Name AS VARCHAR(MAX)

SET @Search_Name = 'William'
SET @Country_Region_Name='United States'

SELECT
FirstName
, MiddleName
, LastName
, CountryRegionName
FROM HumanResources.vEmployee
WHERE
( FirstName  like '%' + @Search_Name + '%'
OR
MiddleName like '%' + @Search_Name + '%'
OR
LastName   like '%' + @Search_Name + '%'
)
AND CountryRegionName = @Country_Region_Name
--OUTPUT

errormsg11508.1

Once the query is executed successfully, lets pass the above query to sp_describe_undeclared_parameters to find undeclared parameters.

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql =
N'SELECT * FROM HumanResources.vEmployee
WHERE
( FirstName  like ''%'' + @Search_Name + ''%''
OR
MiddleName like ''%'' + @Search_Name + ''%''
OR
LastName   like ''%'' + @Search_Name + ''%''
) AND CountryRegionName = @Country_Region_Name'
--OUTPUT

Msg 11508, Level 16, State 1, Line 5
The undeclared parameter ‘@Search_Name’ is used more than once in the batch being analyzed.

Ooopps…… I am unable to execute it.

Resolution:

If you look at the ERROR, you will easily realize that @Search_Name is used as a parameter in the query multiple times. But if you remove @Search_Name from the query, the purpose of the query would be dead. So what is the remedy? It is very simple to fix, just pass @Search_Name parameter in the sp_describe_undeclared_parameters as an input parameter, and the error message will be gone. Lets do it.

USE AdventureWorks2012
GO
sp_describe_undeclared_parameters @tsql =
N'SELECT * FROM HumanResources.vEmployee
WHERE
( FirstName  like ''%'' + @Search_Name + ''%''
OR
MiddleName like ''%'' + @Search_Name + ''%''
OR
LastName   like ''%'' + @Search_Name + ''%''
) AND CountryRegionName = @Country_Region_Name'
,@params =N'@Search_Name nvarchar(50)'
--OUTPUT

errormsg11508.2

Conclusion :

Remember, whenever you come across this error message just search for that particular parameter name causing error in the query and pass it into the sp_describe_undeclared_parameters as an input parameter. There is a possibility that multiple parameters can be placed in the query in multiple times. So to fix this error just pass all the parameters mentioned in the error message and run sp_describe_undeclared_parameters again.

Read Full Post »

SQL SERVER – Shorter way to convert Minutes to Days, Hours, Minutes

Posted in Solutions, SQL SERVER, tagged , , , on May 21, 2013| Leave a Comment »

In one of my earlier articles I wrote about  How to convert seconds to Day(s), Hour(s), Minute(s), Second(s) . Today I am going to talk about creating a simple script to convert the Minutes to Day(s), Hour(s) & Minute(s). Generally, to achieve this result, you need to perform lots of divisions and remainders and then convert Minutes to Day, Hour, Minute. Given below is the simple script to convert Minutes without doing any divisions and remainders, but using only built-in SQL functions DateAdd, DatePart, DateDiff

--This script will work on SQL Server 2005 and above.
DECLARE @Minutes AS INT
DECLARE @SubtractDate AS DATETIME
--Enter Minutes here
SET @Minutes=2990
SET @SubtractDate=DATEADD(mi,@Minutes,GETDATE()) - GETDATE()

SELECT CONVERT(VARCHAR(10),DATEDIFF(DAY,'1900-01-01',@SubtractDate))
+ ' Day(s) ' +
CONVERT(VARCHAR(10),DATEPART(hh,@SubtractDate))
+ ' Hour(s) ' +
CONVERT(VARCHAR(10),DATEPART(mi,@SubtractDate))
+ ' Minute(s) ' AS [Result]
--OUTPUT

Result
———————————
2 Day(s) 1 Hour(s) 50 Minute(s)

(1 row(s) affected)

Read Full Post »

Older Posts »