calculate number of days

• tony appleby

  SSC Enthusiast

  Points: 158

  More actions

  December 26, 2007 at 9:27 am

  #377740

  Hi all,

  I am a newbie here and I 'd like to learn how to:

  a) I want to create a stored procedure that can take a startdate and an enddate and return number of days between those 2 dates. Two dates could span over weeks or months or years.

  b) after creating the SP, how do I run the SP from my Query Analyzer and check out the number of days.

  Any specific examples would be much appreciated.

  Any COldfusion calling the stored procedure with those 2 params and a return value would also be appreciated.

  Tony

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  December 26, 2007 at 9:37 am

  #762709

  Look up datediff in Books Online. It takes two parameters (dates) and a scale (day, week, etc. ) and returns the number of the scale in between the dates.

  select (d, '1/1/2007', getdate())

  To create a stored procedure, you'd do something like this:

  create procedure MyStoredProc

  @date1 datetime

  , @date2 datetime

  as

  select @date1, @date2

  return

  Then you would execute this in Query Analyzer as

  exec MyStoredProc '1/1/2007', '2/1/2007'

  and get the results. Not sure about the call from Cold Fusion. Maybe this: http://articles.techrepublic.com.com/5100-22-1058991.html

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  December 26, 2007 at 11:04 am

  #762749

  I agree with Steve, but the code is so simple I wouldn't make a stored procedure or function for it... too much overhead for such a simple thing.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)