From a set of data in SQL Server database, we needed to find a way to quickly get a numeric representation (add a field MonthNo) of a month name in order to do a comparison. Given we only have a month name and year fields here’s a simple way to do it.

Original SQL:

SELECT [Id], [Month], [Year] 
FROM Table1

Add a field called MonthNo:

MONTH(CONVERT(NVARCHAR, [Month]) 
	+ ' ' + CONVERT(NVARCHAR, [Year])
) AS MonthNo

Final SQL with the new column:

SELECT [Id]
	  , MONTH(CONVERT(NVARCHAR, [Month]) + ' ' 
	  + CONVERT(NVARCHAR, [Year])) AS [MonthNo]
	  , [Month], [Year] 	
FROM Table1

Further Reading

MONTH (Transact-SQL)

A simple way to calculate the duration of a running program with a start and end time. The information should be presented in an hour:minutes:seconds format with leading zeroes (i.e., 00:00:00). Two local variables are used to demonstrate the query.

Typically, this is more useful if done as a stored procedure.

DECLARE @StartTime DATETIME = '2013-07-16 13:02:29';
DECLARE @EndTime DATETIME;
DECLARE @Duration INT;

SET @EndTime = '2013-07-16 15:29:31';

SET @Duration = DATEDIFF(second, @StartTime, @EndTime);

SELECT RIGHT('0' + CONVERT(VARCHAR(2), @Duration/3600), 2) 
   + ':' + RIGHT('0' + CONVERT(VARCHAR(2), @Duration%3600/60), 2) 
   + ':' + RIGHT('0' + CONVERT(VARCHAR(2), @Duration%60), 2) 
AS Duration;

The result here is printed with a column heading Duration and a value of 02:27:02.

Further Reading

DECLARE @local_variable (Transact-SQL)
DATEDIFF (Transact-SQL)

A quick way to populate data from one table to another using Transact-SQL. The objective is to create a copy of the table and populating it at the same time and then create an SQL script to refresh the data when necessary. This can be achieved by using two similar SQL (three if we want to include the TRUNCATE command).

The first run we use “SELECT INTO” statement to query the source table and create a new table and populate it at the same time.

SELECT [ID]
      ,[System Manufacturer]
      ,[Computer Type]
      ,[Computer Model]
      ,[Name]
      ,[Serial Number]
      ,[Image]
      ,[OS]
      ,[OS Revision]
      ,[Architecture]
      ,[Primary User]
      ,[Domain]
      ,[Last Logon User]
      ,[Last Logon Domain]
      ,[Client Date] 
INTO [dbo].[DiscoveryAgentCopy] 
FROM [dbo].[DiscoveryAgent]
GO

You can use * if you want to copy all fields from the table.

SELECT * INTO [dbo].[DiscoveryAgentCopy] 
FROM [dbo].[DiscoveryAgent]
GO

If the copy table is already existed and we want to refresh only the data, we use “INSERT SELECT” statement to read the data from source and map them to the fields on destination table.

-- CLEAR COPY TABLE OF ALL DATA
TRUNCATE TABLE [dbo].[DiscoveryAgentCopy]
GO

-- POPULATE DATA
INSERT INTO [dbo].[DiscoveryAgentCopy] (
       [ID]
      ,[System Manufacturer]
      ,[Computer Type]
      ,[Computer Model]
      ,[Name]
      ,[Serial Number]
      ,[Image]
      ,[OS]
      ,[OS Revision]
      ,[Architecture]
      ,[Primary User]
      ,[Domain]
      ,[Last Logon User]
      ,[Last Logon Domain]
      ,[Client Date])
SELECT [ID]
      ,[System Manufacturer]
      ,[Computer Type]
      ,[Computer Model]
      ,[Name]
      ,[Serial Number]
      ,[Image]
      ,[OS]
      ,[OS Revision]
      ,[Architecture]
      ,[Primary User]
      ,[Domain]
      ,[Last Logon User]
      ,[Last Logon Domain]
      ,[Client Date]
FROM [dbo].[DiscoveryAgent]
GO