Check for error when converting date on insert query

1

Hi there. I am trying to convert dates from an old DB to my new structure.
The old structure stored dates as yyyymmdd so Im using

INSERT INTO NewPersonTable(
NAME_First,
NAME_Surname,
DATE_BirthDate,
Select
Cname,
Surname,
CASE WHEN BIRTHDT <> 0 THEN convert(datetime,cast(BIRTHDT as varchar(8)),112) ELSE NULL END,
from OldPersonTable

All works well....IF the user saved the date correctly.
However, I have a few 'dates' that cannot be 'translated' (eg: 2000222 - should be something like 20000422)
So I am wanting to check for errors and pass a 0 if there is a problem.

I have tried to replace the date conversion line with

if @@error(convert(datetime,cast(BIRTHDT as varchar(8)),112)))> 0
0;
ELSE
convert(datetime,cast(BIRTHDT as varchar(8)),112))
END;

and also tried a Try_Convert like...

try_convert(datetime,convert(datetime,cast(BIRTHDT as varchar(8)),112)),
0,
datetime,convert(datetime,cast(BIRTHDT as varchar(8)),112))

But i cant seem to get this working either.
Are you able to assist me please?

2

try_parse

3 
create table dbo.NewPersonTable(
 NAME_First Varchar(50)
 ,NAME_Surname varchar(50)
 ,DATE_BirthDate datetime
)

Insert into dbo.NewPersonTable(Name_First,Name_Surname,Date_BirthDate)
SELECT 'AAA','BBB','19700202' UNION ALL
SELECT 'Name_First','Name_Surname',ISNULL(Try_Convert(datetime,'2000222',112),Convert(datetime,0,112))

SELECT * FROM dbo.NewPersonTable
NAME_First NAME_Surname DATE_BirthDate
AAA BBB 02/02/1970 00:00:00
Name_First Name_Surname 01/01/1900 00:00:00
4

Thanks Stepson
Although I could not get your solution to work, it set me on the right path and eventually got the following to work.

  case when try_convert(datetime,BIRTHDT,112) is null 
  then convert(datetime,cast(BIRTHDT as varchar(8)),112)
  else 0 END as Dob,
5

Try this :

ISNULL(Try_Convert(datetime,BIRTHDT,112),Convert(datetime,0,112)) as Dob

or your way:

case when try_convert(datetime,BIRTHDT,112) is NOT null 
  then convert(datetime,cast(BIRTHDT as varchar(8)),112)
  else 0 END as Dob

You should use is NOT null
case when try_convert(datetime,BIRTHDT,112) is NOT null

dbfiddle