SQL Server – How to Get Exact Year Difference Between Dates

sql server

I have a date of birth column to which I need to compare the current date and return the difference as age. Currently I am using the following query :

DECLARE @DateOfBirth date = '2000-07-27'
DECLARE @CurrentDate date = '2015-07-25'

SELECT 
    @Age = DATEDIFF(YEAR, @DateOfBirth, @CurrentDate)

I am getting a difference as 15, but the desired result should be 14. This is because it compares the year and gets the difference between 2015 and 2000. So I used the following query :

select 
    @Age = CAST(datediff(day, @DOB, @CurrentDate) / 365.2425 AS INT)
RESULT : 14

But when I change the Date Of Birth value to '2000-07-25', I still get the value of 14, but it should be 15 since the date difference is exactly 15 years.

How can I get the exact year difference from this?

Best Answer

Subtract one from each other in YYYYMMDD format, and divide by 10000 using integer division (which rounds down).

So:

(YEAR(@CurrentDate) * 10000 + MONTH(@CurrentDate) * 100 + DAY(@CurrentDate)
- YEAR(@DateOfBirth) * 10000 - MONTH(@DateOfBirth) * 100 - DAY(@DateOfBirth)
) / 10000

20150725-20000727 = 149998, and 149998 / 10000 = 14

You can also use CONVERT(int,CONVERT(char(8),@CurrentDate,112)), because style 112 is YYYYMMDD, but that's a little obfuscated in my opinion.

Related Solutions

SQL Server – How to Combine Date and Time to datetime2

This seems to work and keep the precision as well:

SELECT DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7)))

The CAST to DATETIME2(7) converts the TIME(7) value (@T) to a DATETIME2 where the date part is '1900-01-01', which is the default value of date and datetime types (see datetime2 and the comment^* at CAST and CONVERT page at MSDN.)

^{* ... When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.}

The DATEADD() and DATEDIFF()function take care of the rest, i.e. adding the difference in days between the 1900-01-01 and the DATE value (@D).

Test at: SQL-Fiddle

As noticed by @Quandary, the above expression is considered not-determninistic by SQL Server. If we want a deterministic expression, say because it is to be used for a PERSISTED column, the '19000101'^** needs to be replaced by 0 or CONVERT(DATE, '19000101', 112):

CREATE TABLE date_time
( d DATE NOT NULL,
  t TIME(7) NOT NULL,
  dt AS DATEADD(day, 
                DATEDIFF(day, CONVERT(DATE, '19000101', 112), d), 
                CAST(t AS DATETIME2(7))
               ) PERSISTED
) ;

^{**: DATEDIFF(day, '19000101', d) isn't deterministic as it does an implicit conversion of the string to DATETIME and conversions from strings to datetime are deterministic only when specific styles are used.}

SQL Server ISNULL – Returning 1900-01-01 When Field is Null

You can't get an empty string because you're returning the DATE value type from ISNULL.

Per MSDN, ISNULL

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

If you're checking whether or not the value is NULL, there is no need to convert it to a date, unless you wanted to return a date value (which you don't seem to).

Instead, use:

SELECT ISNULL( DOB , '')

Which will return

''

if the value is NULL.

A NULL date is NULL (no value). An empty string, on the other hand, evaluates to 0, which in SQL Server is implicitly an integer representing the number of days since 1900-01-01.

Best Answer

Related Solutions

SQL Server – How to Combine Date and Time to datetime2

SQL Server ISNULL – Returning 1900-01-01 When Field is Null

Related Question