SQL DBA Blog

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

Get Rightmost Word In Column

Get Row Counts by Date

Converting VARCHAR to DATE

Splitting And Grouping By Months

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