What is the SQL expression for the following?

[zhuanyi]

Hi,
What if I need the SQL expression for the following:
31-Dec-2007 - DateofBirth + DateofHire?
I tried to use Datediff and dateAdd and it does not seem to allow me to incorporate more than 2 dates.

Thanks!

 

[zhuanyi]

By the way, I would like the ending results to be in years please.

Thanks!

 

[raskew]

Hi -

What is the result of your computation supposed to indicate?

Bob

 

[The_Doc_Man]

The expression will depend on the data type of two fields implied in your question.

If they are both date fields, you can use

DatePart( "Y", (CDate( "31-Dec-2007" ) - [DateOfBirth] + [DateOfHire] ) )

(or something like that) as an expression. The answer will be in the units you specify for DATEPART. It is been a while since I used it so I'm shooting from the hip. However, that should do your trick.

Now, if either of the date fields is actually just a text field with a date string in it, you also need to use CDate() to convert THOSE dates as well.

 

[raskew]

Hi -

Depending on how you show the DatePart() interval:

DOB = #4/1/53#
DOH = #7/15/99#
? DatePart( "y", (CDate( "31-Dec-2007" ) - DOB + DOH))
104
? DatePart( "yyyy", (CDate( "31-Dec-2007" ) - DOB + DOH))
2054

Added:
? #12/31/07# - DOB + DOH
4/14/2054

? datevalue("12/31/07") - dob + doh
4/14/2054

I'm still mystified as to what the result of the computation is supposed to indicate (the year you turn 101, maybe) and/or how it would be used.

Bob