MS Access "DateSerial" equivalent in SQL Server?

I've always used

Age: DateDiff("yyyy",[DoB],Date())+(Date()<DateSerial(Year(Date()),Month([DoB]),Day([DoB])))

in my MS Access queries to create a calculated column for a person's age. But there appears to be no DateSerial function in SQL Server. Any thoughts?...
Who is Participating?
What you need to achieve? Only years? Or moths, days...
You can always form date string that will be implicitly converted to date when used in expression with dates.
sumo_the_catAuthor Commented:
Just need a calculated Age column, really. The above is the function I use in Access.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Anthony PerkinsCommented:
Please maintain these old open questions:

1 02/05/2004 500 Simple stock control database - SQL help...  Open Microsoft Access
2 02/06/2004 500 Tricky SQL help needed!  Open Databases
3 02/20/2004 500 Relational DB design - similar enitities...  Open Databases

first you may use convert function insted of DateSerial and  use GETDATE insted of date()
 another note you can't use < in the sql

actualy i prefer using somthing like
CEILING(DateDiff("m",[DoB],GetDate())/12.0 )
sumo_the_catAuthor Commented:
Eek! - I'd completely forgotten about those q's. They were for a project at a place where I don't work any more. I'll try an sort them!
sumo_the_catAuthor Commented:
cheers mono - i'll use that if i have to, but I'd rather get a neat single line formula for a calculated column rather than a user-defined function, if possible.

waelothman - could you explain that expression? (I'm unfamiliar with CEILING.)
... and lazy enough to not read help about it.
Anthony PerkinsCommented:
This is the page that BAlexandrov is referring to':

Returns the smallest integer greater than, or equal to, the given numeric expression.

CEILING ( numeric_expression )


Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types
Returns the same type as numeric_expression.

This example shows positive numeric, negative, and zero values with the CEILING function.

SELECT CEILING($123.45), CEILING($-123.45), CEILING($0.0)

Here is the result set:

--------- --------- -------------------------
124.00    -123.00    0.00                    

(1 row(s) affected)

sumo_the_catAuthor Commented:
I guessed roughly what CEILING did. Sorry, I meant if you could explain the expression, not particularly the CEILING function, because I'm not sure I see how it is supposes to get the right Age. For example,

SELECT CEILING(DateDiff("m",'04/14/1980',GetDate())/12.0 )

gives me a value of 24, but I'm not 24 until Wednesday..!

BTW, how do you access Help? Enterprise manager Help just gives lots on the Microsoft Management Console but nothing on SQL server.
Anthony PerkinsConnect With a Mentor Commented:
From the Start menu:
Programs | Microsoft SQL Server | Books On line
From EM:
Action | Help
it get the month diffrenet and since yeay =12 monthes we divide on 12 it may get 12.25 this mean  12 years and 3 months se CELING will round it to 13
BAlexandrovConnect With a Mentor Commented:
Here what I have come out

Declare @dob datetime
Declare @current_date datetime

Set @dob = '2003/04/12'
Set @current_date = getdate()

Select datediff(yy,@dob,@current_date) +
case when datediff(dd,cast(0 as datetime),@current_date - cast(cast(DATEPART(year, @current_date) as varchar) + '/' + cast(DATEPART(month, @dob) as varchar) + '/' + cast(DATEPART(day, @dob) as varchar) as datetime))<0 then -1 else 0 end

P.S. DoB of my son is 6 April 2004 and thats the reason that I do not have much time :)
Anthony PerkinsCommented:
>>i'll use that if i have to, but I'd rather get a neat single line formula for a calculated column rather than a user-defined function, if possible.<<
I think you will have to agree that the UDF suggested by monosodiumg is looking better every minute :)  Besides once you have it written:

SELECT dbo.fn_getage(DoB,getdate()) ...

It certain looks a lot simpler.
I agree. This is the exact purpose of UDFs.
sumo_the_catAuthor Commented:
Morning everyone,

Well I will take advice and go with the user-defined function then. However, BAlexandrov provided a single line that works. Waelothman, I don't think yours works - please correct me if i'm wrong.

Thanks for everyone's help,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.