다음을 통해 공유

T-SQL: How to find incorrect datetime data from "Char" format column

  • 아티클

Introduction

One of my colleagues was asked me about problem finding incorrect data but their problem is this column is Char type and find incorrect rows by date type.

Solution

For example, have one table:

CREATE TABLE  #test (c1 char(8) NULL)
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')

In this insert process have a row '20150229' does not match  DateTime because in 2015 February is 28th day.

How select this incorrect rows automatically without difficult queries or without cross or inner join?

For this problem, Microsoft SQL Server has an awesome function called isdate().

If correct time isdate() it give us 1 and for an incorrect time it gives us 0.

Then we need to select our table for incorrect rows.

CREATE TABLE  #test (c1 char(8) NULL)
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
INSERT #test VALUES ('20150131')
INSERT #test VALUES ('20000131')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('20150229')
INSERT #test VALUES ('19990331')
INSERT #test VALUES ('20000331')
 
 
SELECT *
FROM #test
WHERE ISDATE(c1)=0
 
c1
20150229
20150229
20150229
20150229
20150229
20150229

The result was a success in table six rows do not match in DateTime.