help with sql date query

Question

I have date field that holds the birthdate.

I want to calculate the age somthing like [today] - [born date]

How can I do it with a SQL Server 2008 query ?

And why does the date look like 01-02-2009 when I need it 01/02/2009; how do I achieve that?

Thanks a lot for any help

Answer 1

DATEDIFF link

Date representation depends on system culture settings.

Answer 2

Use:

SELECT 
  DATEDIFF(YEAR, [Born Date], getdate())

to calculate the number of years of age. This is just a number (e.g. 55), so what's the problem with the date formatting?

If you want to make sure not to make people older than they are :-) you could also try this:

SELECT 
   FLOOR(DATEDIFF(MONTH, [Born Date], GETDATE()) / 12.0)

Then you'll get the "previous" age up to your birthday (you'll be 54 'til your birthday comes along, and 55 only once you've passed your birthday).

You can format the date representation by using the CONVERT function:

SELECT getdate(), convert(varchar(50), getdatE(), 103)

gives you:

2009-08-08 14:02:33.143                 08/08/2009

Check out the SQL Server Books Online for the various ways of converting a DATETIME to a VARCHAR() representation - there are MANY!

Marc

Answer 3

Use datediff to calculate the age. Then you can use convert to set the style of the datetime type, like so:

select
    convert(varchar(25), getdate(), 101) as Today,
    floor(datediff(MONTH, born_date, getdate()) / 12) as Age
from
    table

101 gives you mm/dd/yyyy. 103 would give you dd/mm/yyyy. And, if you subtract those by 100, you can get mm/dd/yy for 1, or dd/mm/yy for 3. There's a whole table on the convert page.

Answer 4

Just using DATEDIFF is not enough to calculate someone's age, because it rounds up.

Here's a working example on one of my personal databases:

SELECT
    FirstName,
    DATEDIFF(yyyy, DateOfBirth, GETDATE()) - 
    CASE WHEN
        MONTH(DateOfBirth) > MONTH(GETDATE()) OR
        (MONTH(DateOfBirth) = MONTH(GETDATE()) AND
        DAY(DateOfBirth) > DAY(GETDATE()))
    THEN 1 ELSE 0 END
FROM Contact
    WHERE DateOfBirth IS NOT NULL

Yes, it's ugly, but people wouldn't appreciate it if I make them out to be older than they actually are :)

I'm not sure how the date format is relevant when dealing with ages. The outcome is a simple integer.

Answer 5

This will get around the off-by-one error you can get with datediff(yyyy or mm):

SELECT 
  floor((cast(getDate() AS float) - cast([birthdate] AS float))/365.25)

You can then recast it as whatever type you want (int, varchar, etc.)

You could use

floor(datediff(DAY, [birthdate], getDate())/365.25)

but I'm in the habit of casting dates to floats for added flexibility (especially when making complex comparisons).

The date-reformat question has been well-answered, but just to be complete:

convert(varchar(10), getdate(), 103)