Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS Access "DateSerial" equivalent in SQL Server?

Posted on 2004-04-11
16
Medium Priority
?
3,605 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 800 total points
ID: 10800836
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

916 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