August 27, 2008 at 8:55 am
Hello, All.
I've been given an interesting assignment, to translate 3 columns into an actual date of birth to put into a table as a datetime datatype. There are 4 columns, BirthMonth, BirthDay, Age and RegistrationDate. All of them are varchars of one flavor or another. Birthday is supposed to literally only be the day while BirthMonth is the month. The easy part is to take the DateAdd of RegistrationDate minus the years in Age to come up with the BirthYear.
I've cleaned up the BirthMonth and BirthDay columns, converted RegistrationDate into a datetime, and verified the Age column (which has a lot of values over 100, but a WHERE clause takes care of this issue). Unfortunately, when I throw them all together and try and convert them, I'm getting an error saying I can't convert some of the character types to datetime.
Here's the code I have so far:
Select BirthMonth + '/' + BirthDay + '/' +
Convert(char(4),Year(DateAdd(yyyy,Age,RegistrationDate))) as BDay
from MyTable
It's when I put a Convert(Datetime, xxxxx) around the whole thing with the xxxxx equaling my expression that I run into problems. All the values look okay at a quick glance, but there are over 24,000 which is way too many for me to search manually for the offending records. So I threw it all into a temp table and went searching for bad dates and added:
Select Bday
from #MyTemp
where Substring(Bday,1,5) not in
('06/31','04/31','09/31','11/31','02/30','02/31')
or (Substring(Bday,1,5) in ('02/29')
and Substring(Bday,7,4) not in (...long list of leap years...) )
I figured the conversion would work if I excluded the 31st for months I know don't have a 31st and coded for the February issue. Right? Nope, still coming up with an error.
Did I miss a possible date issue? I'm pretty sure all the values are formatted correctly, but I can't even get a SELECT to work because of these values (which means I can't pinpoint which record in the set is causing the first problem).
Any ideas on how I can iterate through this to locate bad "date" values and get the PK it's related to? PK is an Identity field, BTW.
Thanks in advance,
August 27, 2008 at 9:24 am
Hey Brandie,
You may want to try using the ISO date format (yyyymmdd) when building the date string that way the DATEFORMAT settings don't come into play.
Have you tried using the IsDate() function? Something like:
[font="Courier New"]SELECT
pk,
BDay
FROM
#MyTable
WHERE
ISDATE(CONVERT(VARCHAR(15), bDay, 101) <> 1[/font]
I used the convert function because according to BOL:
ISDATE is deterministic only if used with the CONVERT function, the CONVERT style parameter is specified and style is not equal to 0, 100, 9, or 109.
The return value of ISDATE may be affected by LANGUAGE and DATEFORMAT settings.
For examples of templates for which ISDATE will return 1, see the Input/Output column of the "Arguments" section of CAST and CONVERT.
ISDATE returns 1 if the input expression is a valid date; otherwise, it returns 0. The following table shows the return values for a selection of examples.
I also noticed that in your example code you are ADDING the Age to the registration date Year(DateAdd(yyyy,Age,RegistrationDate)) to get the birth year and you state you want to subtract that in your text.
The easy part is to take the DateAdd of RegistrationDate minus the years in Age to come up with the BirthYear.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 27, 2008 at 10:20 am
Jack,
RE: The Age subtraction. Yeah, I did subtract it at home, it's just that fumble fingers didn't type it correctly in her post. @=)
ISDATE()! DOH! I knew there had to be a function to check, like ISNUMERIC(), but I went completely BLANK last night when I was coding and couldn't remember.
Thank you so much. I think ISDATE will give me exactly what I need.
You da bomb!
August 27, 2008 at 2:14 pm
The easy part is to take the DateAdd of RegistrationDate minus the years in Age to come up with the BirthYear.
Birth year may not be that easy.
If the birth is on August 31st, the registration date is Sept. 1st 2000, and the age is 20, then the birth year is 1980
If the birth is on Sept 2nd, the registration date is Sept. 1st 2000, and the age is 20, then the birth year is 1979 ! The person had a age of 20 on Sept 2nd, 1999 but will not be 21 until the day after the registration.
SQL = Scarcely Qualifies as a Language
August 28, 2008 at 4:21 am
Yeah. I know, Carl. But when I said it was easy, I meant that it's hard to make the result of a Year function unconvertible to a datetime. I knew my problems had to be from the month and the day parts of the equation.
ISDATE() was what I couldn't remember and what solved my problem without having to compensate for leap years, 31sts on months that only go to 30, or having to manually search 24,000 records for an offending alpha character.
What really kills me is the database setup. Fields that should be datetime are varchar(20) or varchar(50). So are fields that should be int, smallint or tinyint (or they're nvarchar() for no apparent reason). And there's no filtering / input mask on the programming side let alone check constraints on the DB. I spend more of my time fixing data issues than I do on the actual assigned contract work.
I've told the client there's need for a full time DB, but it's a small shop and they really really like being able to call me on an irregular basis. @sigh. Oh, well. Extra spending money on top of my regular paycheck is always good. @=)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply