Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access "DateSerial" equivalent in SQL Server?

Posted on 2004-04-11
16
Medium Priority
?
3,598 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 800 total points
ID: 10800836
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

704 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