Technical Article

Checking All Date Columns in a Table(s)


Summary:  An ETL process which moved data from a new application to a legacy one, encountered errors in date columns.  Since there were multiple data types and insufficient validation by the user application, a quick check of numerous date columns was necessary.  This stored procedure provides a quick scan of all columns in a table or range of tables, displaying the minimum and maximum values for all Date, DateTime, and SmallDateTime columns.

Detail:  A new application for tracking IT projects used a DATE datatype for the numerous dates recorded in any given project.  Unfortunately, since DATE allows values from "0001-01-01 through 9999-12-31" ( ) and the user application did no validation, a mistyped date caused issues for an SSIS process that moved data to legacy tables in which som columns were datatype SmallDateTime.  This datatype will only accept values from "1900-01-01 through 2079-06-06" ( )

Since the error can be the results of values in several dozen columns, a quick snapshot of minimum and maximum values seemed useful.  This stored procedure provides that quick inspection.  Here is an example of its usage - note that one of the columns will pose a problem:

/****** Object:  StoredProcedure [dbo].[usp_TblDateMinMax]    Script Date: 10/27/2014 9:02:53 AM ******/SET ANSI_NULLS ON


-- =====================================================
-- Author:Larry Schmidt
-- Create date: October 21, 2014
-- Description:Find the maximum and minimum dates
--                 in each of the date columns in
--   all Scorecard tables SCD_Projectxxx
-- =====================================================
CREATE PROCEDURE [dbo].[usp_TblDateMinMax] @TableMask varchar(max)


CREATE Table #DateResults (ID int,TblCol varchar(max), Maxdt date, Mindt date)

select IDENTITY(INT, 1,1) AS [ID], AS Tbl, AS Col
  into #Scorecardtmp
from syscolumns c
INNER JOIN sysobjects o ON =
where o.xtype = 'U' and c.xusertype in (40,58,61) and like @TableMask and <> 'RowInsertDate' and <> 'LastModifiedDate'
order by,

SET @MaxRownum = (SELECT MAX(ID) FROM #Scorecardtmp)

SET @Iter = 1

DECLARE @SQLCmd varchar(max), @Maxdt date, @Mindt date, @Tbl varchar(max), @Col varchar(max), @ID int

 WHILE @Iter <= @MaxRownum
     SELECT @ID = ID, @Tbl = Tbl, @Col = Col from #Scorecardtmp WHERE ID = @Iter
 INSERT INTO #DateResults (ID, TblCol) VALUES (@Iter,'Table: ' + @Tbl + ', Column: ' + @Col)
 SET @SQLCmd = 'UPDATE #DateResults SET Mindt = (SELECT min(' + @Col + ') from ' + @Tbl + ') where ID = ' + CAST(@Iter as varchar)
 exec (@SQLCmd)
 SET @SQLCmd = 'UPDATE #DateResults SET Maxdt = (SELECT max(' + @Col + ') from ' + @Tbl + ') where ID = ' + CAST(@Iter as varchar)
     exec (@SQLCmd)
     SET @Iter = @Iter + 1

select * from #DateResults

drop table #Scorecardtmp
drop table #DateResults


3.86 (7)




3.86 (7)