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.
Calling Stored Procedures With Datetime Parameters
From Wiki
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
- CREATE PROCEDURE prGetDateYearAgo
- @DATE DATETIME
- AS
- SET NOCOUNT ON
- SELECT DATEADD(yy,-1,@DATE)
- GO
We already know that this doesn't work
- 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
- 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
- DECLARE @d DATETIME
- SELECT @d = GETDATE()
- EXEC prGetDateYearAgo @d
And here we are calling the proc with named parameters
- DECLARE @d DATETIME
- SELECT @d = GETDATE()
- EXEC prGetDateYearAgo @DATE = @d
Contributed by: --SQLDenis 17:07, 30 May 2008 (GMT)
Part of SQL Server Programming Hacks
Section Dates
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.