|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
GetDate not working if passedIf 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 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: http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp Datetimes http://www.murach.com/books/sqls/article.htm Datetime Searching -- Show quoteAndrew J. Kelly SQL MVP "tshad" <tscheider***@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 I did have the datatype set as DateTime: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: @system tinyint, @date_to_send datetime, @from varchar(256), but when I called the SP using GetDate(), I get the error. Tom Show quote > http://www.karaszi.com/SQLServer/info_datetime.asp Guide to Datetimes > http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp > Datetimes > http://www.murach.com/books/sqls/article.htm Datetime Searching > > > -- > Andrew J. Kelly SQL MVP > > > "tshad" <tscheider***@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 >> > > 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 -- Show quoteAndrew J. Kelly SQL MVP "tshad" <tscheider***@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... >> 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/columnists/bsyverson/sqldatetime.asp >> Datetimes >> http://www.murach.com/books/sqls/article.htm Datetime Searching >> >> >> -- >> Andrew J. Kelly SQL MVP >> >> >> "tshad" <tscheider***@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 The SP is something like: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. > 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 Show quote > EXEC dbo.Yoursp > > > -- > Andrew J. Kelly SQL MVP > > > "tshad" <tscheider***@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... >>> 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/columnists/bsyverson/sqldatetime.asp >>> Datetimes >>> http://www.murach.com/books/sqls/article.htm Datetime Searching >>> >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> >>> >>> "tshad" <tscheider***@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 >>>> >>> >>> >> >> > > > 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."Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message I did see it, I was just responding to Andrew. I think he misunderstood news:%23GkUpt7MGHA.516@TK2MSFTNGP15.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. 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? No, did you try it?> > @secondParam DATETIME = GetDate() See Aaron's reply.
-- Show quoteAndrew J. Kelly SQL MVP "tshad" <tscheider***@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... >> 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" <tscheider***@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... >>>> 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/columnists/bsyverson/sqldatetime.asp >>>> Datetimes >>>> http://www.murach.com/books/sqls/article.htm Datetime Searching >>>> >>>> >>>> -- >>>> Andrew J. Kelly SQL MVP >>>> >>>> >>>> "tshad" <tscheider***@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/info_datetime.asp#DtFormatsInput Show quote "tshad" <tscheider***@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 >  |
|||||||||||||||||||||||
Other interesting topics