Check valid Date for all Columns in all tables for current DB

Question

Post reply

Check valid Date for all Columns in all tables for current DB

info 58414

Hall of Fame

Points: 3386
More actions
January 5, 2016 at 2:45 am

#310588

Hi,
for ETL-Processing i need a function, who check all columns in all tables with spec. Name (see code) and Data_Type nvarchar. If the Value isn't a valid Dateformat, then printout....
The Datatype is allways nvarchar (because its a staging table)
I have a little codesnippet addet... 🙂
Thanks
Regards Nicole 😉
;with cte
AS
(
SELECT COLUMN_NAME, TABLE_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE (COLUMN_NAME LIKE '%date%' or COLUMN_NAME like '%create%')
and DATA_TYPE = 'nvarchar'
)
Select all columns from all cte.tables where column.value != korrekt Dateformat;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 1

You really need to post some sample data

You could use isdate()

declare @Table table

(DateColumn nvarchar(20))

insert into @Table

select '2015-01-01' union all

select '31/01/2015' union all

select '01/31/2015' union all

select '2015-01-01 00:00:00' union all

select '2015-01-01 11:26:35' union all

select 'XYZ' union all

select null

select DateColumn, isdate(DateColumn) as ValidDate

from @Table

DateColumn ValidDate

-------------------- -----------

2015-01-01 1

31/01/2015 0

01/31/2015 1

2015-01-01 00:00:00 1

2015-01-01 11:26:35 1

XYZ 0

NULL 0

Note it won't work for UK format unless you have modifed the language/dateformat. Maybe this isn't an issue for you though.

info 58414 Hall of Fame Points: 3386 More actions · Answer 2

hi, i know isdate()-Function. I need this for all Tables, Columns...

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Nicole

You say it's for ETL processing - are you using SSIS? If so, you might consider weeding out the invalid values in your data flow. There are ways of doing it in T-SQL, but you'll end up with a lot of ugly-looking dynamic SQL.

John

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Nicole

You say it's for ETL processing - are you using SSIS? If so, you might consider weeding out the invalid values in your data flow. There are ways of doing it in T-SQL, but you'll end up with a lot of ugly-looking dynamic SQL.

John