Solved

MS Access "DateSerial" equivalent in SQL Server?

Posted on 2004-04-11
16
3,592 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

623 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