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

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it?
Pls reply as soon as possible.
Cheers
Hi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:

> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it?
> Pls reply as soon as possible.
> Cheers
>

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/XML/ExportSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:
> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>

Get the XML out of sql server 2005 in c#

Hi,
is there a way to get the result of select query which uses or xml
auto, elements to c# ?
for ex, i have a query like
"SELECT * from dbo.[user] where userid = @.UserID for xml auto,
elements"
and i want result of this query back to c# function, how can i do it'
Pls reply as soon as possible.
CheersHi
You may find something at
http://www.perfectxml.com/Articles/...ortSQLXML.asp#5
http://sqlxml.org/faqs.aspx?1 or
http://support.microsoft.com/kb/q271620/
John
"steven" wrote:

> Hi,
> is there a way to get the result of select query which uses or xml
> auto, elements to c# ?
> for ex, i have a query like
> "SELECT * from dbo.[user] where userid = @.UserID for xml auto,
> elements"
> and i want result of this query back to c# function, how can i do it'
> Pls reply as soon as possible.
> Cheers
>

Get statistics on db objects

I'm using something like:

SELECT COUNT(*) 'Number of System Tables' FROM dbo.sysobjects

WHERE xtype = 's'

to get statistics on database objects but I seem to remember that theres a more efficient way. For example, is there some way to add something like a where clause to the first table returned by sp_help (where Object_type = 'view', for example)?

Thanks,

Dave

SP_TABLES can be used in this csae with parameters for '

Table, system table, or view.'

get record with earliest datetime value

Hello all,

Quick sql syntax question:

I have this table:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO

CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

with these values:

insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)

I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE

I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE

not sure if this is the way to go...

pointer appreciated
thanksHow about doing this:
1: Change Row_ID from NULL to NOT NULL
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NOT NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO

2: Add value for column ROW_ID in INSERT:
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 1)
insert into REQUESTS (ROW_ID, REQUEST_DATE, STATUS)
values (NEWID(), getdate(), 0)

3: Use correct table name in SELECT - from REQUEST_LOG to REQUESTS
SELECT TOP 1 ROW_ID FROM REQUESTs WHERE STATUS = 0 ORDER BY
REQUEST_DATE

On Feb 9, 9:59 am, "hharry" <paulquig...@.nyc.comwrote:

Quote:

Originally Posted by

Hello all,
>
Quick sql syntax question:
>
I have this table:
>
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
[REQUESTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[REQUESTS]
GO
>
CREATE TABLE [dbo].[REQUESTS] (
[ROW_ID] [uniqueidentifier] NULL ,
[REQUEST_DATE] [datetime] NULL ,
[STATUS] [tinyint] NULL
) ON [PRIMARY]
GO
>
with these values:
>
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 1)
insert into REQUESTS (REQUEST_DATE, STATUS)
values (getdate(), 0)
>
I need to select the single record with a STATUS = 0 with the earliest
REQUEST_DATE
>
I am using this query:
SELECT TOP 1 ROW_ID FROM REQUEST_LOG WHERE STATUS = 0 ORDER BY
REQUEST_DATE
>
not sure if this is the way to go...
>
pointer appreciated
thanks

|||apologies for the typos

what i should have asked is this:

Is TOP applied after the ORDER BY or before...can someone confirm
this ?|||Yes, TOP is applied after the result set rows are ordered with ORDER BY.

Regards,

Plamen Ratchev
http://www.SQLStudio.com