Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old February 27th, 2006, 09:38 PM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Calculate age from a varchar birthdate field

Hello there,
I have a birth date field in my database(which is a varchar field) and I need to calculate range of ages, I have tried to convert the varchar to datetime field directly in the database, but it is not letting me, then I tried various ways, one of them is below:

Select first_nm as 'First Name',last_nm as 'Last Name',
 Cast(DateDiff("mm", Convert(varchar(10),birthdt,101), Convert(varchar(10),GetDate(),101))/12 AS INTEGER) as age
I keep getting the error
"Syntax error converting datetime from character string"
Please help....!! I am going NUTS...!!
Reply With Quote
  #2 (permalink)  
Old February 28th, 2006, 10:37 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SQL Server works better when dates, including birthdates, are stored as datetime data types instead of varchar.

Once you have the birth dates stored as datetime data, the following function can be used to calculate age:

Code:
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_AgeCalc]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_AgeCalc]
GO




CREATE FUNCTION [dbo].[f_AgeCalc] (
@DateOfBirth DateTime = NULL,
@RefDate DateTime = NULL)
RETURNS int AS  
BEGIN
RETURN (SELECT CASE
WHEN ((@DateOfBirth IS NULL) OR (@RefDate IS NULL))
THEN NULL
WHEN (@DateOfBirth > @RefDate) THEN NULL
WHEN DateADD(yyyy, DateDiff(yyyy, @DateOfBirth, @RefDate), @DateOfBirth) > @RefDate
THEN DateDiff(yyyy, @DateOfBirth, @RefDate) - 1
ELSE DateDiff(yyyy, @DateOfBirth, @RefDate) 
END)
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
Call the function as:

SELECT dbo.f_CalcAge(BirthDt, GetDate()) [Age]

Rand
Reply With Quote
  #3 (permalink)  
Old February 28th, 2006, 10:04 PM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thank you very much for your help Rand. I am very new at designing store procedures and functions. How do I convert the varchar field to a datetime field? This was designed by someone else and I tried to edit the database design to convert to datetime but I am getting an odbc error. Can't I use a convert function in this function?
Reply With Quote
  #4 (permalink)  
Old March 1st, 2006, 04:44 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

What is the format of your dob-string?

Gert

Reply With Quote
  #5 (permalink)  
Old March 1st, 2006, 11:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Naperville, IL, USA.
Posts: 196
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I just tested my function - described earlier with varchar(10) data. It works!!!

Code:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[t_Date3]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [t_Date3]
GO

IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[t_Date3]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
 BEGIN
CREATE TABLE [t_Date3] (
    [ID] [int] identity NOT NULL ,
    [Date3] varchar(10) NULL 
) ON [PRIMARY]
END

GO

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('01/01/2001')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('02/29/2004')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('03/01/2004')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('03/02/2004')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('03/01/2000')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('12/31/1999')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('04/01/2003')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('02/07/1998')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('12/24/1937')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('05/08/1948')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('03/03/2003')

INSERT INTO [RandTest].[dbo].[t_Date3]([Date3])
VALUES('02/29/1960')
GO

SELECT [ID], [Date3] [Birth Date], dbo.f_AgeCalc([Date3], GetDate()) [Age] FROM [RandTest].[dbo].[t_Date3]
GO
Rand
Reply With Quote
  #6 (permalink)  
Old March 2nd, 2006, 10:45 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Rand, i would think that it depends of the format he has saved his date of birth...
ymd
dmy
mdy

etc, etc..

There are a lot of different formats, and not all use the form that is normal in USA (month-day-year).

Gert

Reply With Quote
  #7 (permalink)  
Old March 4th, 2006, 01:29 PM
Registered User
 
Join Date: Mar 2006
Location: Nashville, tn, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You need to create the following User defined function in SQL and setup a virtual field in your table called Age (or whatever):
(Just create new function, paste the following in there and save)

create function CalculateAge(@dDOB datetime, @dCurrentDate datetime) returns int as
begin
   declare @iAge int

   set @iAge = datediff(year, @dDOB, @dCurrentDate)

   -- See if birtdate is before current date.
   -- If so, subtract one from age.

   if @dCurrentDate > @dDOB
      set @iAge = @iAge - 1

   return @iAge
end

Next, add a new field to your table called age.
In the "formula" property of the field do this:
(dbo.Calculateage([birthdate],getdate()))

That's it. Use the Age field in anything without ever having to calculate anything in any code.




Reply With Quote
  #8 (permalink)  
Old March 6th, 2006, 03:58 PM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Gert
 What is the format of your dob-string?

Gert

Reply With Quote
  #9 (permalink)  
Old March 6th, 2006, 04:07 PM
Authorized User
 
Join Date: Feb 2006
Location: , , .
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hello, Thank you very much for that tip, the idea of creating a virtual field was great. But in the formula field it is saying the formula is wrong.
My date of birth is in varchar format "08/08/1965" so I tried using your function two ways, one changing the input in the function to varchar and then converting to datetime
  set @iAge = datediff(year,Convert(varchar(20),@dDOB,101),@dCur rentDate)
then I tried coverting it in the formula field as:(dbo.Calculateage([Convert(varchar(20),birthdate,101)],getdate()))
None of them are working.
What am I doing wrong? Please help.

Quote:
quote:Originally posted by JoeFlow
 You need to create the following User defined function in SQL and setup a virtual field in your table called Age (or whatever):
(Just create new function, paste the following in there and save)

create function CalculateAge(@dDOB datetime, @dCurrentDate datetime) returns int as
begin
   declare @iAge int

   set @iAge = datediff(year, @dDOB, @dCurrentDate)

   -- See if birtdate is before current date.
   -- If so, subtract one from age.

   if @dCurrentDate > @dDOB
      set @iAge = @iAge - 1

   return @iAge
end

Next, add a new field to your table called age.
In the "formula" property of the field do this:
(dbo.Calculateage([birthdate],getdate()))

That's it.  Use the Age field in anything without ever having to calculate anything in any code.  




Reply With Quote
  #10 (permalink)  
Old March 7th, 2006, 03:17 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi.

If you could answer my question "What is the format of the date of birth string?", then we might be able to help.
You say it is in format "08/08/1965", but that doesn't tell us mutch. What is the date-part and what is the month-part?
In your example the result will always be august 8th 1965, but how would you write august 20th 1965 in your format?
"20/08/1965" or "08/20/1965" ?

Also: what errormessages do you get from the functions?

Gert

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Age Calculation rgerald SQL Server 2000 32 October 10th, 2007 02:12 AM
age function keyvanjan ASP.NET 1.0 and 1.1 Basics 2 August 30th, 2006 09:46 AM
Vacancy Age alannoble26 Excel VBA 2 November 23rd, 2005 03:05 AM
Query birthdate by month Batusai Access 2 February 23rd, 2005 01:23 AM
calculate value on Lost Focus of text field creative_eye General .NET 1 April 12th, 2004 09:35 AM



All times are GMT -4. The time now is 07:59 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.