Stored procedure moving dates.

• Aardvark-878532

  SSC Enthusiast

  Points: 159

  More actions

  September 17, 2008 at 9:13 am

  #71094

  Hi,

  I am calling a procedure that requires two dates sets to run Eg exec my_proc '08/08/08/ , '14/08/08'.

  Would it be possible to write into the stored procedure that the default dates would always be between today and today -7?. This would remove the requirement enter the dates when calling the procedure.

  Any help would be most appreciated.

• Andrew Gothard-467944

  Hall of Fame

  Points: 3908

  More actions

  September 17, 2008 at 9:30 am

  #873090

  CREATE FUNCTION [dbo].[TRUNC]

  (

  @datInDATETIME

  )

  RETURNS DATETIME

  AS

  BEGIN

  RETURN CAST(FLOOR(CAST( @datIn AS float))AS datetime)

  END

  CREATE PROCEDURE spMyProc

  (

  @datStartDATETIME= NULL

  )

  AS

  DECLARE @datEndDATETIME

  SELECT @datStart = COALESCE(@datStart, dbo.TRUNC(GETDATE()))

  SELECT @datEnd = DATEADD(Day, -7, @datStart)

  etc ....

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  September 17, 2008 at 8:01 pm

  #873345

  You might want to consider using 4 digit years and, possibly, a date format where the month and day cannot be confused... for example, is 06/07/2008 June 7th or July 6th?

  --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)
  Intro to Tally Tables and Functions