silchester
asked on
Difference Between Two Dates Stored Procedure SQL
Hi
I am trying to do my first stored procedure in SQL Server Management 2005 Express. I basically want to take two dates from a table of batch numbers and calculate the time span between the two dates. (The dates will be the first and last datetime that the batch number was recorded in table).
I have my code so far, but it does not compile - throwing incorrect syntax error on the line:
set @firstdate = SELECT top 1 datetimecol from Test_01.Table_1 where batchcol = @batchnumber order by datetimecol ASC
Even then I am not sure I am going the correct route. any hints / opinions would be appreciated.
Cheers
Neil Whitley
I am trying to do my first stored procedure in SQL Server Management 2005 Express. I basically want to take two dates from a table of batch numbers and calculate the time span between the two dates. (The dates will be the first and last datetime that the batch number was recorded in table).
I have my code so far, but it does not compile - throwing incorrect syntax error on the line:
set @firstdate = SELECT top 1 datetimecol from Test_01.Table_1 where batchcol = @batchnumber order by datetimecol ASC
Even then I am not sure I am going the correct route. any hints / opinions would be appreciated.
Cheers
Neil Whitley
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE TimeDifferenceSecs
-- Add the parameters for the stored procedure here
@batchnumber int = 99,
@vessel varchar(50) = "??",
@firstdate as datetime,
@lastdate as datetime,
@difference as datetime
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set @firstdate = SELECT top 1 datetimecol from Test_01.Table_1 where batchcol = @batchnumber order by datetimecol ASC
set @lastdate = SELECT top 1 datetimecol from Test_01.Table_1 where batchcol = @batchnumber order by datetimecol DES
set @difference = Datediff("s",@firstdate,@lastdate)
END
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you'll need to call the procedure and pass parameters to it.
http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx
http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx
ASKER
Thanks for that - have sorted the results grid query too.
ASKER
Sorry for the stupid question.
Neil