CMES-IT
asked on
Executing a Stored Procedure with a DATE
Hi,
I've got a stored procedure:
CREATE PROCEDURE TestTable.spTest
@StartDateGreaterThan SMALLDATETIME,
@StartDateLessThan SMALLDATETIME
AS
SELECT * FROM TestTable
WHERE StartDate > @StartDateGreaterThan AND StartDate < @StartDateLessThan
When I try to execute it, via:
EXECUTE TestTable.spTest 1-1-1900, 1-1-2100
I get:
Incorrect syntax near '-'.
Here's what else I've tried:
EXECUTE TestTable.spTest '1-1-1900', '1-1-2100'
-> Error converting data type varchar to smalldatetime.
EXECUTE TestTable.spTest #1-1-1900#, #1-1-2100#
-> Incorrect syntax near '-'.
EXECUTE TestTable.spTest '1/1/1900', '1/1/2100'
-> Error converting data type varchar to smalldatetime.
EXECUTE TestTable.spTest 1/1/1900, 1/1/2100
-> Incorrect syntax near '/'.
What is the correct way to call this stored procedure?
I've got a stored procedure:
CREATE PROCEDURE TestTable.spTest
@StartDateGreaterThan SMALLDATETIME,
@StartDateLessThan SMALLDATETIME
AS
SELECT * FROM TestTable
WHERE StartDate > @StartDateGreaterThan AND StartDate < @StartDateLessThan
When I try to execute it, via:
EXECUTE TestTable.spTest 1-1-1900, 1-1-2100
I get:
Incorrect syntax near '-'.
Here's what else I've tried:
EXECUTE TestTable.spTest '1-1-1900', '1-1-2100'
-> Error converting data type varchar to smalldatetime.
EXECUTE TestTable.spTest #1-1-1900#, #1-1-2100#
-> Incorrect syntax near '-'.
EXECUTE TestTable.spTest '1/1/1900', '1/1/2100'
-> Error converting data type varchar to smalldatetime.
EXECUTE TestTable.spTest 1/1/1900, 1/1/2100
-> Incorrect syntax near '/'.
What is the correct way to call this stored procedure?
EXECUTE TestTable.spTest '1900-01-01', '2100-01-01'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Angel,
Thanks,
This:
EXECUTE TestTable.spTest '1900-01-01', '2100-01-01'
Produces: Error converting data type varchar to smalldatetime.
And This:
DECLARE @s datetime
DECLARE @e datetime
SET @s = CONVERT(datetime, '1900-01-01', 120)
SET @e = CONVERT(datetime, '2100-01-01', 120)
EXECUTE TestTable.spTest @s, @e
Produces:
Error converting data type varchar to smalldatetime.
However, that second one got me on the right track.
The correct syntax is:
DECLARE @s smalldatetime
DECLARE @e smalldatetime
SET @s = CONVERT(smalldatetime, '1950-01-01', 120)
SET @e = CONVERT(smalldatetime, '2050-01-01', 120)
EXECUTE Accounting.spAccountCodeTe st @s, @e
That works!
Thanks for getting me on the right track ;)
Thanks,
This:
EXECUTE TestTable.spTest '1900-01-01', '2100-01-01'
Produces: Error converting data type varchar to smalldatetime.
And This:
DECLARE @s datetime
DECLARE @e datetime
SET @s = CONVERT(datetime, '1900-01-01', 120)
SET @e = CONVERT(datetime, '2100-01-01', 120)
EXECUTE TestTable.spTest @s, @e
Produces:
Error converting data type varchar to smalldatetime.
However, that second one got me on the right track.
The correct syntax is:
DECLARE @s smalldatetime
DECLARE @e smalldatetime
SET @s = CONVERT(smalldatetime, '1950-01-01', 120)
SET @e = CONVERT(smalldatetime, '2050-01-01', 120)
EXECUTE Accounting.spAccountCodeTe
That works!
Thanks for getting me on the right track ;)