Solved

MS Access "DateSerial" equivalent in SQL Server?

Posted on 2004-04-11
16
3,554 Views
Last Modified: 2010-05-18
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?...
0
Comment
Question by:sumo_the_cat
  • 5
  • 4
  • 4
  • +2
16 Comments
 
LVL 6

Expert Comment

by:BAlexandrov
ID: 10800660
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.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10800781
Just need a calculated Age column, really. The above is the function I use in Access.
0
 
LVL 12

Accepted Solution

by:
monosodiumg earned 200 total points
ID: 10800836
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10800845
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

Thanks.
0
 
LVL 5

Expert Comment

by:waelothman
ID: 10800906
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 )
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10801141
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!
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10801215
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.)
0
 
LVL 6

Expert Comment

by:BAlexandrov
ID: 10801364
... and lazy enough to not read help about it.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10801376
This is the page that BAlexandrov is referring to':

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

Syntax
CEILING ( numeric_expression )

Arguments
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.

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

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

Here is the result set:

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

(1 row(s) affected)

0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10801592
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.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 10801619
From the Start menu:
Programs | Microsoft SQL Server | Books On line
From EM:
Action | Help
0
 
LVL 5

Expert Comment

by:waelothman
ID: 10801947
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
0
 
LVL 6

Assisted Solution

by:BAlexandrov
BAlexandrov earned 200 total points
ID: 10802081
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 :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10802126
>>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.
0
 
LVL 6

Expert Comment

by:BAlexandrov
ID: 10803931
I agree. This is the exact purpose of UDFs.
0
 
LVL 2

Author Comment

by:sumo_the_cat
ID: 10804393
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,
Peter.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now