datediff function - cannot execute query

• ritesh.kansara

  SSChasing Mays

  Points: 654

  More actions

  October 26, 2009 at 7:55 am

  #96019

  Hi I'm trying to do a datediff function in sql server 2005.

  I want to use similar coding i've done in access :

  Age: DateDiff("yyyy",[DOB],[DOD])+Int(Format([DOD],"mmdd")<Format([DOB],"mmdd"))

  This doesn't work. I've also tried using the

  datediff ((G.DOB)- (G.DOD ))AS age1

  I get an error when I execute:

  Msg 174, Level 15, State 1, Line 1

  The datediff function requires 3 arguments. HELP

• Steve Jones - SSC Editor

  SSC Guru

  Points: 738043

  More actions

  October 26, 2009 at 9:06 am

  #1070754

  You need three arguments

  http://msdn.microsoft.com/en-us/library/ms189794%28SQL.90%29.aspx

  Use Books Online.

• Silverfox

  SSC-Insane

  Points: 23150

  More actions

  October 26, 2009 at 9:16 am

  #1070765

  use year as the datepart argument when using datediff and your example will work. once you have modified it, based on the link supplied by Steve.

  --------------------------------------------------------------------------------------
  [highlight]Recommended Articles on How to help us help you and[/highlight]
  [highlight]solve commonly asked questions[/highlight]

  Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
  Managing Transaction Logs by Gail Shaw[/url]
  How to post Performance problems by Gail Shaw[/url]
  Help, my database is corrupt. Now what? by Gail Shaw[/url]

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  October 26, 2009 at 9:24 am

  #1070773

  The code posted here is not mine, but was posted by another SSC contributor whose name I have forgotten ... but not their contribution, and I thank them for that.

  Returns a person age in years.

  --calculate age in years

  declare @dob datetime, -- date of birth

  @age int,

  @day datetime

  set @day = GETDATE() --Returns the current date time

  set @dob = '1932-04-18'

  set @age = datediff(yy,@dob,@day) -

  case when @day < dateadd(yy,datediff(yy,@dob,@day), @dob) then 1 else 0 end

  select @age

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• ritesh.kansara

  SSChasing Mays

  Points: 654

  More actions

  October 26, 2009 at 10:32 am

  #1070816

  thanks