GetDate not working if passed

I am calling a SP that is expecting a datetime.
If I pass '02/15/06/, it works fine.
If I do it like:
****************************************
**************
Declare @.DateToSend VarChar(20)
Select @.DateToSend = GetDate()
Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,@.DateToSend
****************************************
******************
This works.
But if I do
Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,GetDate()
I get the error:
Server: Msg 170, Level 15, State 1, Line 63
Line 63: Incorrect syntax near ')'.
Why doesn't this work?
Thanks,
TomBecause GETDATE() returns a DATETIME datatype and you are declaring the
parameter as a VARCHAR. Change it to a DATETIME and it should be fine. The
reason it works passing the string is that SQL Server will implicity convert
a proper string to a DATETIME datatype but not the other way around. And
you should get in the habit of using the ISO or ANSI format for date or
datetime strings. See here for more details:
http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/col...sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uXws$OzMGHA.1676@.TK2MSFTNGP09.phx.gbl...
>I am calling a SP that is expecting a datetime.
> If I pass '02/15/06/, it works fine.
> If I do it like:
> ****************************************
**************
> Declare @.DateToSend VarChar(20)
> Select @.DateToSend = GetDate()
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,@.DateToSend
> ****************************************
******************
> This works.
> But if I do
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,GetDate()
> I get the error:
> Server: Msg 170, Level 15, State 1, Line 63
> Line 63: Incorrect syntax near ')'.
> Why doesn't this work?
> Thanks,
> Tom
>|||You can't pass a function as a parameter. You need to store it in a
variable in the interim. Or, make the parameter optional, e.g.
CREATE PROCEDURE dbo.COM_INSERT_MESSAGE_TO_QUEUE_SP
@.firstParam INT,
@.secondParam DATETIME = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT @.secondParam = COALESCE(@.secondParam, GETDATE());
..
END
GO
Also, '02/15/06' is a horrible, horrible, horrible date format. I recommend
you get in the habit of using unambiguous formats.
http://www.karaszi.com/SQLServer/in...#DtFormatsInput
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:uXws$OzMGHA.1676@.TK2MSFTNGP09.phx.gbl...
>I am calling a SP that is expecting a datetime.
> If I pass '02/15/06/, it works fine.
> If I do it like:
> ****************************************
**************
> Declare @.DateToSend VarChar(20)
> Select @.DateToSend = GetDate()
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,@.DateToSend
> ****************************************
******************
> This works.
> But if I do
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,GetDate()
> I get the error:
> Server: Msg 170, Level 15, State 1, Line 63
> Line 63: Incorrect syntax near ')'.
> Why doesn't this work?
> Thanks,
> Tom
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OnOVqTzMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> Because GETDATE() returns a DATETIME datatype and you are declaring the
> parameter as a VARCHAR. Change it to a DATETIME and it should be fine.
> The reason it works passing the string is that SQL Server will implicity
> convert a proper string to a DATETIME datatype but not the other way
> around. And you should get in the habit of using the ISO or ANSI format
> for date or datetime strings. See here for more details:
I did have the datatype set as DateTime:
@.system tinyint,
@.date_to_send datetime,
@.from varchar(256),
but when I called the SP using GetDate(), I get the error.
Tom
> http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes
> http://www.sqlservercentral.com/col...sqldatetime.asp
> Datetimes
> http://www.murach.com/books/sqls/article.htm Datetime Searching
>
> --
> Andrew J. Kelly SQL MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:uXws$OzMGHA.1676@.TK2MSFTNGP09.phx.gbl...
>|||Can you post the actual code for the sp and exactly how you called it? By
the way you should also get in the habit of qualifying all objects
especially sp's with the owner.
EXEC dbo.Yoursp
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e$iq650MGHA.208@.tk2msftngp13.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OnOVqTzMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> I did have the datatype set as DateTime:
> @.system tinyint,
> @.date_to_send datetime,
> @.from varchar(256),
> but when I called the SP using GetDate(), I get the error.
> Tom
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eYOYZg1MGHA.140@.TK2MSFTNGP12.phx.gbl...
> Can you post the actual code for the sp and exactly how you called it? By
> the way you should also get in the habit of qualifying all objects
> especially sp's with the owner.
>
The SP is something like:
CREATE PROCEDURE dbo.COM_INSERT_MESSAGE_TO_QUEUE_SP
(
@.system tinyint,
@.date_to_send datetime
)
Called like:
Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,GetDate()
Doesn't work.
Called like:
****************************************
**************
Declare @.DateToSend VarChar(20)
Select @.DateToSend = GetDate()
Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,@.DateToSend
****************************************
******************
Does work.
If GetDate() passes a DateTime, why doesn't it work in the Exec statement?
Also, all my SP are all owned by dbo and are called by my Web Server. This
seems to work fine. Why should I need to add the dbo.?
Thanks,
Tom

> EXEC dbo.Yoursp
>
> --
> Andrew J. Kelly SQL MVP
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e$iq650MGHA.208@.tk2msftngp13.phx.gbl...
>|||> If GetDate() passes a DateTime, why doesn't it work in the Exec statement?
Did you see my reply? You can't pass a function into a parameter.|||See Aaron's reply.
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7Ba9B3MGHA.1192@.TK2MSFTNGP11.phx.gbl...
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eYOYZg1MGHA.140@.TK2MSFTNGP12.phx.gbl...
> The SP is something like:
> CREATE PROCEDURE dbo.COM_INSERT_MESSAGE_TO_QUEUE_SP
> (
> @.system tinyint,
> @.date_to_send datetime
> )
> Called like:
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,GetDate()
> Doesn't work.
> Called like:
> ****************************************
**************
> Declare @.DateToSend VarChar(20)
> Select @.DateToSend = GetDate()
> Exec COM_INSERT_MESSAGE_TO_QUEUE_SP 1,@.DateToSend
> ****************************************
******************
> Does work.
> If GetDate() passes a DateTime, why doesn't it work in the Exec statement?
> Also, all my SP are all owned by dbo and are called by my Web Server.
> This seems to work fine. Why should I need to add the dbo.?
> Thanks,
> Tom
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23GkUpt7MGHA.516@.TK2MSFTNGP15.phx.gbl...
> Did you see my reply? You can't pass a function into a parameter.
I did see it, I was just responding to Andrew. I think he misunderstood
what I was asking as he said I had the parameter set as VarChar and I was
showing how I had it set.
BTW, if you set a parameter as optional, can you use GetDate() there?
@.secondParam DATETIME = GetDate()
I don't think you can, but just curious.
Thanks,
Tom|||> BTW, if you set a parameter as optional, can you use GetDate() there?
> @.secondParam DATETIME = GetDate()
No, did you try it?