December 10, 2011 at 8:20 pm
I would write something in a stored procedure.
If age<23, but we don't have age colum, but only a birthdate column, I do by below,
ROUND((DATEDIFF(mm,CAST([Birthdate] AS DATE),GETDATE()) / 12),0) <18 ,
is this correct? I did a cast part because orginally this column is a varchar
also the round part, I am not sure what does 0 mean?
Thanks,
or any other ways of rewriting the below.
December 10, 2011 at 9:15 pm
Computing the age of someone is more difficult than it might seem when you take into account different month lengths, leap year, and other things.
This function returns age in years.
Age Function F_AGE_IN_YEARS:
December 11, 2011 at 6:46 am
My rules of thumb for date/datetime data:
1) always store in the appropriate data type (date / datetime)
2) don't put functions on columns, so if you need e.g. 23 year old use
where mydate > dateadd(yy, -23, getdate() )
in stead of "where datediff(yy, mydate, getdate()) >= 23 " because the latter one is not considered to be sargeble and indexes on your mydate column cannot be used in an optimal way to solve this criteria.
3) stick with your column in date/datetime data type as long as you can. Meaning some people tend to convert to (var)char data type because they want to hand over formatted data to their calling app. Let the calling app handle the formatting itself !
4) PLEASE DO NOT PROVIDE A DATE/DATETIME HANDLING FUNCTION AS IT HIDES WHAT ONE REALLY WANTS TO DO FOR ANYONE TROUBLESHOOTING THE QUERY AND IT MAY EVEN PREVENT THE OPTIMIZER TO GENERATE AN OPTIMAL PLAN.
( shouting intended, but not in an offensive way! It is a warning. )
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
December 11, 2011 at 8:14 am
You don't really need a function for this although you could certainly put it in a function for programming consistency.
SELECT DATEDIFF(yy,BirthdayColumn,GETDATE())
- CASE
WHEN GETDATE() < DATEADD(yy,DATEDIFF(yy,BirthdayColumn,GETDATE()),BirthdayColumn)
THEN 1
ELSE 0
END
FROM dbo.SomeTable
I agree with Johan, though. If you don't need the actual number of years to be displayed, his solution would be the better way to go.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 11, 2011 at 10:50 am
Thanks all for the ideas.
The reason I use cast the birthdate is because the birthdate column is varchar in a staging table.
The staging table comes from a ssis package load from flat file source.
we load the raw data in and keep the default varchar50 to the staging table, and didn't do the data type conversion in ssis, for the raw data helps us later in troubleshooting.
Then we use a stored procedure convert the data type and insert the data from the staging table into real target table.
And also in the sproc we filter out the birthdate <23
December 11, 2011 at 3:39 pm
I think that adding a WHERE in your stored procedure will work
e.g.
UPDATE table
SET col1 = 'some value'
WHERE DATEDIFF(yy,Birthdate,getdate()) < 23
unless i miss something...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply