views:

1310

answers:

3

There are two things here i want to accomplish.

  • List I want to pass a datetime into my stored procedure in the following format ('2007-05-28 00:00:00'), but that doesn't seem to work unless i use {ts '2007-05-28 00:00:00'}

  • The next thing is I want to increase the @SEAL_DATE by a second so it can be used to check the dates between the entered time and a second after.

Thanks in advance

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[getSealRecordDate]  (@SEAL_DATE DATETIME)
    AS
-- Declare variables


 SELECT DISTINCT "FGFSTRANSFERS"."CONSIDERATION", "FGFSTRANSFERS"."AMOUNT_TRANSFER", "FGFSTRANSFERS"."DATE", "FGFSTransferDetails"."TRANSFER_ID", "FGFSTRANSFERS"."TRANSFER_ID", "FGFSTRANSFERS"."Curr", "FGFSTransferDetails"."CURR", "FGFSCUSTOMERS"."CMF_NAME_1", "FGFSCUSTOMERS"."CMF_NAME_2"
 FROM   ("FGFSInvestment"."dbo"."FGFSTransferDetails" "FGFSTransferDetails" INNER JOIN "FGFSInvestment"."dbo"."FGFSTRANSFERS" "FGFSTRANSFERS" ON "FGFSTransferDetails"."TRANSFER_ID"="FGFSTRANSFERS"."TRANSFER_ID") INNER JOIN "FGFSInvestment"."dbo"."FGFSCUSTOMERS" "FGFSCUSTOMERS" ON "FGFSTRANSFERS"."CUSTODIAN"="FGFSCUSTOMERS"."CMF_ACCOUNT"
 WHERE  ("FGFSTRANSFERS"."DATE">= @SEAL_DATE AND "FGFSTRANSFERS"."DATE"<{ts '2007-05-28 00:00:01'})

 -- EXEC getSealRecordDate {ts '2007-05-28 00:00:00'}

Pseudo-SQL so it should be like: WHERE ("FGFSTRANSFERS"."DATE">= @SEAL_DATE AND "FGFSTRANSFERS"."DATE"< @SEAL_DATE+ 1 sec ) <-- Something like that

A: 

For your second part, use DATEADD

DateAdd (second, 1, @DateVariable)

Raj More
+1  A: 
DATEADD(second, 1, @SEAL_DATE)
Chris Klepeis
+1  A: 

Take a look How Does Between Work With Dates In SQL Server?

use dateadd to add a second

select dateadd(second,1,'20090625 00:00:00.000')
SQLMenace