I have a table in my database with multiple fields. Two of those fields are alt_id set to int and birthdateset to datetime. Right now all of the data for the birthday field is null. The data in the alt_id field is listed like 09191982.
Obviously the alt_id fields are all birthdates. I need to get this data in the birthdate field as well.
I am not sure as to how to proceed.
I tried something like:
update clients set birthdate = substring(alt_id, 5, 4) + '-' + substring(alt_id, 1, 2) + '-' + substring(alt_id, 3, 2)
But get an error:
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
I am assuming one of the dates being updated doesn't exist but there are 845 records and I am unsure as to how to check and see which one it is.
I did do a couple of simple things, checking for 8 characters, checking to make sure the all comformed to '____19__'. I am assuming it may be something like a date '1988-06-31' which doesn't exist, just need some help to rule that out.
Unless I am doing this wrong and there is an easier way to do it, I'm all ears.
Thanks!
update clients set birthdate = substring(alt_id, 5, 4) + '/' + substring(alt_id, 1, 2) + '/' + substring(alt_id, 3, 2)