MS SQL Server - Using current time as parameter for a stored procedure

Author

Using current time as parameter for a stored procedure

Ib Schrader

2006-12-05, 5:16 am

Hi there

I have a stored procedure that calculates no. of calls handled in a helpdesk
within a given interval. The stored procedure accepts the paramaters @start
and @end which tells it the endpoints of the interval.

I'm trying to create a web page that shows the no. of calls handled today up
till now. Meaning that the @start should be midnight yesterday and @end
should be the time right now.

Until now I've experimented with using GETDATE to get the current time, but
I don't seem to be able to get the syntax correct. I've also tried to get
the stored procedure to accept values in a table as parameters but I also
get a "incorrect syntax" error here. What I'm asking is this:

Does anyone have any ingenious idea of how to feed midnight and the time
right now as parameters to the stored procedure?

If not

Does anyone know the correct syntax for using GETDATE as a parameter for a
stored procedure and/or the correct syntax for reading a table value and
using it as a parameter?

Any help greatly appreciated, thanks
Ib

2006-12-05, 7:12 pm

Try following code

declare @yesterday varchar(20)
set @yesterday=cast(cast
(day(getdate())-1 as varchar) + '/'
+cast(month(getdate(
)) as varchar) +'/ '+cast(year(getdate(
)) as varchar) + '
00:00:000' as datetime)

select count(1) from table_1 where date between @yesterday and getdate()

vt

"Ib Schrader" <ibschrader@gmail.com> wrote in message
news:eb5hIeFGHHA.4804@TK2MSFTNGP03.phx.gbl...
> Hi there
>
> I have a stored procedure that calculates no. of calls handled in a
> helpdesk within a given interval. The stored procedure accepts the
> paramaters @start and @end which tells it the endpoints of the interval.
>
> I'm trying to create a web page that shows the no. of calls handled today
> up till now. Meaning that the @start should be midnight yesterday and @end
> should be the time right now.
>
> Until now I've experimented with using GETDATE to get the current time,
> but I don't seem to be able to get the syntax correct. I've also tried to
> get the stored procedure to accept values in a table as parameters but I
> also get a "incorrect syntax" error here. What I'm asking is this:
>
> Does anyone have any ingenious idea of how to feed midnight and the time
> right now as parameters to the stored procedure?
>
> If not
>
> Does anyone know the correct syntax for using GETDATE as a parameter for a
> stored procedure and/or the correct syntax for reading a table value and
> using it as a parameter?
>
> Any help greatly appreciated, thanks
> Ib
>

Hilary Cotter

2006-12-05, 7:12 pm

or try

declare @getdate datetime
select @getdate=convert(dat
etime, convert(varchar(20),
getdate(),1))
print @getdate

select count(1) from sysobjects where crdate between @getdate and getdate()

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

"vt" <vinu.t.1976@gmail.com> wrote in message
news:uy9w71FGHHA.3468@TK2MSFTNGP04.phx.gbl...
>
> Try following code
>
> declare @yesterday varchar(20)
> set @yesterday=cast(cast
(day(getdate())-1 as varchar) + '/'
> +cast(month(getdate(
)) as varchar) +'/ '+cast(year(getdate(
)) as varchar) +
> ' 00:00:000' as datetime)
>
> select count(1) from table_1 where date between @yesterday and getdate()
>
> vt
>
>
>
>
> "Ib Schrader" <ibschrader@gmail.com> wrote in message
> news:eb5hIeFGHHA.4804@TK2MSFTNGP03.phx.gbl...
>
>

Ib Schrader

2006-12-05, 7:12 pm

Thanks a lot, I got it working using something like this:

declare @midnight datetime
declare @now datetime

select @midnight=convert(da
tetime, convert(varchar(20),
getdate(),1))
select @now=getdate()

exec sp_name @midnight,@now

And of course now they asked for more. Is there a way to see no. of calls
handled only within the last 30 mins?. That is, the start time should not be
midnight, but 30 mins ago?

I appreciate your help.
Ib

Hari Prasad

2006-12-05, 7:12 pm

Hello,

If you have the createdon or modified column with datetime datatype in the
table then you could use the DATEDIFF function...

Select * from table where datediff(mi,getdate(
),createdon)>30

Thanks
Hari

"Ib Schrader" <ibschrader@gmail.com> wrote in message
news:%238CYG0GGHHA.4760@TK2MSFTNGP03.phx.gbl...
> Thanks a lot, I got it working using something like this:
>
> declare @midnight datetime
> declare @now datetime
>
> select @midnight=convert(da
tetime, convert(varchar(20),
getdate(),1))
> select @now=getdate()
>
> exec sp_name @midnight,@now
>
> And of course now they asked for more. Is there a way to see no. of calls
> handled only within the last 30 mins?. That is, the start time should not
> be midnight, but 30 mins ago?
>
> I appreciate your help.
> Ib
>

Ib Schrader

2006-12-05, 7:12 pm

Thanks but I don't want to select those columns younger than 30 mins. I want
to parse the datetime value of 30 mins ago to the stored procedure.

Kinda like this code: select @starttime=convert(d
atetime,
convert(varchar(20),
getdate(),1))
returns the time at midnight. I need something like that..just the time 30
mins ago instead.

Thanks for your reply anyway :)

Ib Schrader

2006-12-05, 7:12 pm

I just discovered the DATEADD command. It lets you add or subtract time from
a DATETIME field. It solved it for me

Ib

Dan Guzman

2006-12-05, 7:12 pm

> Does anyone have any ingenious idea of how to feed midnight and the time
> right now as parameters to the stored procedure?

It's not intuitive but the best (fastest) way I've seen to remove the time
portion from a datetime value:

SELECT
DATEADD(day, 0, DATEDIFF(day, 0, GETDATE()))

> Meaning that the @start should be midnight yesterday and @end should be
> the time right now.

CREATE PROC dbo.GetHandledCalls
@start datetime,
@end datetime
AS
SELECT CallTime, OtherData
FROM dbo.HandledCalls
WHERE
CallTime BETWEEN @start AND @end
GO

DECLARE @start datetime, @end datetime

--calc midnight yesterday
SET @start = SELECT DATEADD(day, -1, DATEDIFF(day, 0, GETDATE()))

--now
SET @end = GETDATE()

EXEC dbo.GetHandledCalls @start, @end

> Does anyone know the correct syntax for using GETDATE as a parameter for a
> stored procedure and/or the correct syntax for reading a table value and
> using it as a parameter?

You can specify only constants or variables as parameters; expressions like
GETDATE() are not permitted.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ib Schrader" <ibschrader@gmail.com> wrote in message
news:eb5hIeFGHHA.4804@TK2MSFTNGP03.phx.gbl...
> Hi there
>
> I have a stored procedure that calculates no. of calls handled in a
> helpdesk within a given interval. The stored procedure accepts the
> paramaters @start and @end which tells it the endpoints of the interval.
>
> I'm trying to create a web page that shows the no. of calls handled today
> up till now. Meaning that the @start should be midnight yesterday and @end
> should be the time right now.
>
> Until now I've experimented with using GETDATE to get the current time,
> but I don't seem to be able to get the syntax correct. I've also tried to
> get the stored procedure to accept values in a table as parameters but I
> also get a "incorrect syntax" error here. What I'm asking is this:
>
> Does anyone have any ingenious idea of how to feed midnight and the time
> right now as parameters to the stored procedure?
>
> If not
>
> Does anyone know the correct syntax for using GETDATE as a parameter for a
> stored procedure and/or the correct syntax for reading a table value and
> using it as a parameter?
>
> Any help greatly appreciated, thanks
> Ib
>

Sponsored Links