Executing Stored Procedure with data time column

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  December 5, 2007 at 9:08 am

  #61473

  I have procedure which takes dates in the form of varchar fields, but when I pass the dates as I get an error

  ALTER procedure P_REALTYTAX (@DATE1 varchar(20), @DATE2 varchar(20))

  as

  EXEC dbo.[P_REALTYTAX] ('2007-12-05', '2007-12-05')

  EXEC dbo.[P_REALTYTAX] ('11/07/07', '11/07/06')

  I get the following error

  Server: Msg 170, Level 15, State 1, Line 1

  Line 1: Incorrect syntax near '2007'.

  Thanks

  Sri

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 5, 2007 at 11:02 am

  #757407

  you shouldn't use varchar fields to carry/pass datetime fields.

  your procedure should accept datetime fields instead:

  ALTER procedure P_REALTYTAX (@DATE1 datetime, @DATE2 datetime)

  as

  ...

  to test whether this would work, simply try sticking the values in a datetime field:

  declare @mydate datetime

  set @mydate='2007-12-05'

  print @mydate

  set @mydate='11/07/07'

  print @mydate

  results:

  Dec 5 2007 12:00AM

  Nov 7 2007 12:00AM

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jeff Moden

  SSC Guru

  Points: 1004365

  More actions

  December 6, 2007 at 12:13 am

  #757538

  The problem is that you should not use parenthesis around parameters for stored procedures. 😉

  --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)