GetDate as Parameter for UDF Function returns table

Help!!
I am trying to pass GetDate() as a paramter into a function that returns a
table.
select * from
dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
This fails but it works if I pass in the date as a string. I need to use
GetDate.
I cannot create a local variable, this is a view.Simply CAST it as a varchar..
....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
Note,
You may wish to parse it out to get only the portions of the date that you
want...
You could try casting it as a decimal as well. That may work better for
you.
Rick Sawtell
MCT, MCSD, MCDBA
"Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> Help!!
> I am trying to pass GetDate() as a paramter into a function that returns a
> table.
> select * from
> dbo.hta2_Calculate_Closed_Inventory_By_Date(
GetDate(),'10/30/2004','Company')
> This fails but it works if I pass in the date as a string. I need to use
> GetDate.
> I cannot create a local variable, this is a view.|||Rick
Your suggestion does not work
CREATE FUNCTION fn_dates(@.dt AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
INSERT INTO @.Dates VALUES(@.dt)
RETURN
END
--Doesnt work (as you suggested)
SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
--Does work
DECLARE @.dt DATETIME
SET @.dt=GETDATE()
SELECT * from dbo.fn_dates (@.dt)
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Simply CAST it as a varchar..
> ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> Note,
> You may wish to parse it out to get only the portions of the date that you
> want...
> You could try casting it as a decimal as well. That may work better for
> you.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
> "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > Help!!
> >
> > I am trying to pass GetDate() as a paramter into a function that returns
a
> > table.
> >
> > select * from
> > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> GetDate(),'10/30/2004','Company')
> >
> > This fails but it works if I pass in the date as a string. I need to
use
> > GetDate.
> >
> > I cannot create a local variable, this is a view.
>|||Can you simply put the GetDate() inside the function?
Rick
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> Rick
> Your suggestion does not work
> CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> INSERT INTO @.Dates VALUES(@.dt)
> RETURN
> END
> --Doesnt work (as you suggested)
> SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> --Does work
> DECLARE @.dt DATETIME
> SET @.dt=GETDATE()
> SELECT * from dbo.fn_dates (@.dt)
>
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > Simply CAST it as a varchar..
> >
> > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> >
> > Note,
> >
> > You may wish to parse it out to get only the portions of the date that
you
> > want...
> >
> > You could try casting it as a decimal as well. That may work better for
> > you.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
> >
> > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > Help!!
> > >
> > > I am trying to pass GetDate() as a paramter into a function that
returns
> a
> > > table.
> > >
> > > select * from
> > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > GetDate(),'10/30/2004','Company')
> > >
> > > This fails but it works if I pass in the date as a string. I need to
> use
> > > GetDate.
> > >
> > > I cannot create a local variable, this is a view.
> >
> >
>|||Rick
I'm sure you know that you cannot use GETDATE() inside the UDF. It's
documented
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:%23RH4ww4tEHA.2948@.TK2MSFTNGP15.phx.gbl...
> Can you simply put the GetDate() inside the function?
> Rick
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23hIeNG1tEHA.820@.TK2MSFTNGP12.phx.gbl...
> > Rick
> > Your suggestion does not work
> >
> > CREATE FUNCTION fn_dates(@.dt AS DATETIME)
> > RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> > AS
> > BEGIN
> > INSERT INTO @.Dates VALUES(@.dt)
> > RETURN
> > END
> >
> > --Doesnt work (as you suggested)
> > SELECT * from dbo.fn_dates (CAST(GETDATE() AS VARCHAR(30)))
> > --Does work
> > DECLARE @.dt DATETIME
> > SET @.dt=GETDATE()
> > SELECT * from dbo.fn_dates (@.dt)
> >
> >
> >
> > "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> > news:%23lC94kutEHA.2948@.TK2MSFTNGP15.phx.gbl...
> > > Simply CAST it as a varchar..
> > >
> > > ....(CAST(GetDate() AS varchar(20)), '10/30/2004', ...
> > >
> > > Note,
> > >
> > > You may wish to parse it out to get only the portions of the date that
> you
> > > want...
> > >
> > > You could try casting it as a decimal as well. That may work better
for
> > > you.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> > >
> > > "Liam Ponder" <liamDOTponderATShaw.ca> wrote in message
> > > news:E7E4FD59-85FF-4CCC-842A-CAA68B89C30F@.microsoft.com...
> > > > Help!!
> > > >
> > > > I am trying to pass GetDate() as a paramter into a function that
> returns
> > a
> > > > table.
> > > >
> > > > select * from
> > > > dbo.hta2_Calculate_Closed_Inventory_By_Date(
> > > GetDate(),'10/30/2004','Company')
> > > >
> > > > This fails but it works if I pass in the date as a string. I need
to
> > use
> > > > GetDate.
> > > >
> > > > I cannot create a local variable, this is a view.
> > >
> > >
> >
> >
>|||Liam,
Table-valued functions can only receive literal constants and variables
as parameters. Functions, expressions, and column references cannot
be used as parameters.
Steve Kass
Drew University
Liam Ponder wrote:
>Help!!
>I am trying to pass GetDate() as a paramter into a function that returns a
>table.
>select * from
>dbo.hta2_Calculate_Closed_Inventory_By_Date( GetDate(),'10/30/2004','Company')
>This fails but it works if I pass in the date as a string. I need to use
>GetDate.
>I cannot create a local variable, this is a view.
>sql