Tech Blog – Scripts – MSSQL ETL Cheat Sheet


Selecting Dates & Times

https://docs.microsoft.com/en-us/sql/t-sql/data-types/date-and-time-types?view=sql-server-ver15

-- Time.
	SELECT CONVERT(TIME(0),GETDATE()) AS Time0Miliseconds;
	-- 15:23:18

	SELECT CONVERT(TIME(3),GETDATE()) AS Time2Miliseconds;
	-- 15:24:31.75

	SELECT CONVERT(TIME, GETDATE()) AS Time7Miliseconds;
	-- 15:24:41.32000

-- Date & Time
	SELECT CONVERT(DATE,GETDATE()) AS DateOnly;
	-- 2019-11-28

	SELECT CAST(GETDATE() AS smalldatetime) AS DateAndTime0Miliseconds;
	-- 2019-11-28 15:26:00

	SELECT GETDATE() AS DateAndTime3Miliseconds;
	-- 2019-11-28  10:15:31.393
	
	SELECT CAST(GETDATE() AS datetime2) AS DateAndTime7Miliseconds;
	-- 2019-11-28 15:28:28.6100000

	SELECT CAST(GETDATE() AS datetimeoffset) DateAndTimeWithTimeZone;
	-- 2019-11-28 15:29:01.2066667 +00:00

Show Only The Domain Of An Email Address

-- Show only the domain of an email address. 
SELECT RIGHT('peter_whyte@outlook.com', LEN('peter_whyte@outlook.com') - CHARINDEX('@', 'peter_whyte@outlook.com')) 
-- OUTPUT::'outlook.com'

Get Rightmost Word In Column

SELECT DISTINCT
         RIGHT(' ' + RTRIM([address]), CHARINDEX(' ', REVERSE(' ' + RTRIM([address]))) - 1) AS LastWord,
         COUNT(RIGHT(' ' + RTRIM([address]), CHARINDEX(' ', REVERSE(' ' + RTRIM([address]))) - 1)) COUNT
FROM 
  [dbo].[tableName]
GROUP BY 
  RIGHT(' ' + RTRIM([address]), 
  CHARINDEX(' ', REVERSE(' ' + RTRIM([address]))) - 1)
ORDER BY 
  COUNT(RIGHT(' ' + RTRIM([address]), 
  CHARINDEX(' ', REVERSE(' ' + RTRIM([address]))) - 1)) DESC

Get Row Counts by Date

USE [Adventureworks]
GO

-- Get Row Counts Per Year.
  SELECT 
    COUNT(*) AS 'Row Count', 
    DATEPART(Year, Cast(OrderDate AS DATE)) AS 'Year' 
  FROM [Dbo].[FactResellerSalesXL_CCI]
  GROUP BY DATEPART(Year, Cast(OrderDate AS DATE))
  ORDER BY 2 DESC

-- Get Row Counts Per Year, Including Months.
  SELECT 
    DATEPART(year, CAST(OrderDate AS DATE)) AS 'Year',
    DATEPART(month, CAST(OrderDate AS DATE)) AS 'Month',
    COUNT(*) AS 'Row Count'
  FROM [Dbo].[FactResellerSalesXL_CCI]
  GROUP BY DATEPART(year, CAST(OrderDate AS DATE)), DATEPART(month, CAST(OrderDate AS DATE))
  ORDER BY 1,2 ASC

Converting VARCHAR to DATE

-- Variations of dates.
SELECT 
  CAST('12/01/2018' AS DATE) -- 2018-12-01
SELECT 
  CAST('12-01-2018' AS DATE) -- 2018-12-01
SELECT 
  CAST('12.01.2018' AS DATE) -- 2018-12-01

-- ## No delimiter (it'll have to be added).
SELECT 
  CAST('12012018' AS DATE) -- Conversion failed when converting date and/or time from character string.

-- ## Adding a delimiter for the date cast.
DECLARE @tmpSTDDate VARCHAR(16) = '2018/12/01' 
SELECT 
  CAST(LEFT(@tmpSTDDate, 4) + '-' + SUBSTRING(@tmpSTDDate, 6, 2) + '-' + RIGHT(@tmpSTDDate, 2) AS DATE) -- 2018-12-01

DECLARE @tmpSTDDate_MDChange VARCHAR(16) = '2018/12/13' 
SELECT 
  CAST(LEFT(@tmpSTDDate_MDChange, 4) + '-' + SUBSTRING(@tmpSTDDate_MDChange, 6, 2) + '-' + RIGHT(@tmpSTDDate_MDChange, 2) AS DATE) -- 2018-12-13

-- ## Reversing date format.
DECLARE @tmpREVDate VARCHAR(16) = '12.01.2018'
SELECT 
  CAST(LEFT(@tmpREVDate, 2) + '-' + SUBSTRING(@tmpREVDate, 4, 2) + '-' + RIGHT(@tmpREVDate, 4) AS DATE) -- 2018-12-01

DECLARE @tmpREVDate_MDChange VARCHAR(16) = '12.13.2018'
SELECT 
  CAST(LEFT(@tmpREVDate_MDChange, 2) + '-' + SUBSTRING(@tmpREVDate_MDChange, 4, 2) + '-' + RIGHT(@tmpREVDate_MDChange, 4) AS DATE) -- 2018-12-13

Splitting And Grouping By Months

-- Get row counts per month, grouped by month and year.
SELECT 
  COUNT(*) AS 'Row Count', 
       CASE
       WHEN DATEPART(month, CAST([date] as date)) = 12 THEN 'December'
       WHEN DATEPART(month, CAST([date] as date)) = 11 THEN 'November'
       WHEN DATEPART(month, CAST([date] as date)) = 10 THEN 'October' 
       WHEN DATEPART(month, CAST([date] as date)) = 9 THEN 'September'
       WHEN DATEPART(month, CAST([date] as date)) = 8 THEN 'August'
       WHEN DATEPART(month, CAST([date] as date)) = 7 THEN 'July'
       WHEN DATEPART(month, CAST([date] as date)) = 6 THEN 'June'
       WHEN DATEPART(month, CAST([date] as date)) = 5 THEN 'May'
       WHEN DATEPART(month, CAST([date] as date)) = 4 THEN 'April'
       WHEN DATEPART(month, CAST([date] as date)) = 3 THEN 'March' 
       WHEN DATEPART(month, CAST([date] as date)) = 2 THEN 'February'
       WHEN DATEPART(month, CAST([date] as date)) = 1 THEN 'January'
       END as 'Month',
       DATEPART(year, CAST([date] as date)) as 'Year'  
FROM 
  [database].[schema].[table]
GROUP BY 
  DATEPART(month, cast([date] as date)), DATEPART(year, cast([date] as date))

Find a String in all Columns & Databases

DECLARE @SQL VARCHAR(MAX) 
DECLARE @valueToFind VARCHAR(100) 
DECLARE @columnName VARCHAR(100) 

SET @valueToFind = '%%' 
SET @columnName = '%%' 

CREATE TABLE #TMP 
   (Clmn VARCHAR(500), 
   CNT INT) 

SELECT @SQL=COALESCE(@SQL,'')+CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME + ''' AS Clmn, count(*) CNT FROM '  
        + TABLE_SCHEMA + '.[' + TABLE_NAME + 
       '] WHERE [' + COLUMN_NAME + '] LIKE ''%' + @valueToFind + '%'' ;'  AS VARCHAR(MAX)) 
FROM INFORMATION_SCHEMA.COLUMNS  
   JOIN sysobjects B  
   ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME 
WHERE COLUMN_NAME LIKE @columnName AND xtype = 'U' 
   AND DATA_TYPE IN ('char','nchar','ntext','nvarchar','text','varchar') 

PRINT @SQL 
EXEC(@SQL) 

SELECT * FROM #TMP WHERE CNT > 0 
DROP TABLE #TMP