Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Calling Stored Procedures With Datetime Parameters

From Wiki

Jump to: navigation, search

How would you pass getdate() to a proc? There is no problem calling a stored procedure with integers and varchars For example prGetvalue 1,'A' but the following won't work prGetvalue getdate() So what can we do? First create this procedure


  1. CREATE PROCEDURE prGetDateYearAgo
  2.     @DATE DATETIME
  3.     AS
  4.     SET NOCOUNT ON
  5.      
  6.     SELECT DATEADD(yy,-1,@DATE)
  7.     GO


We already know that this doesn't work

  1. EXEC prGetDateYearAgo GETDATE()

This is the error message

Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near ')'.


You can hardcode the value and pass it as a string


  1. EXEC prGetDateYearAgo '2007-11-28 11:04:29.373'

That works but you can't always do that since you won't know the exact time and date beforehand.


Another way is to use a datetime variable. Here we are calling the proc with positional parameters


  1. DECLARE @d DATETIME
  2.     SELECT @d = GETDATE()
  3.      
  4.     EXEC prGetDateYearAgo @d


And here we are calling the proc with named parameters


  1. DECLARE @d DATETIME
  2.     SELECT @d = GETDATE()
  3.      
  4.     EXEC prGetDateYearAgo @DATE = @d


Contributed by: --SQLDenis 17:07, 30 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Dates

126 Rating: 2.5/5 (110 votes cast)