Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

Community Wiki

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. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

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.

Navigation

Google Ads

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.8/5 (92 votes cast)