|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stored Procedures and the GetTime() functiondatabase is compared to the current time. Example: If timestamp value is >= (current time - 1 hour) then return 0, else return 1. The purpose of this is to verify that data has been imported in to the table within the last hour. I figured the GetTime() function would provide me with the current time but I'm not sure how to set it up so that it evaluates the current time - 1 hour. Any help would be greatly appreciated. Thanks, -- Grant Smith A+, Net+, MCP x 2 Quality Production Liaison Hewlett Packard Company Database Administrator Renaissance Systems and Services, LLC Hi
SELECT DATEADD(hour, -1, GETDATE()) -- -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ "Grant Smith" <grant.sm***@envent-tech.com> wrote in message news:j06if.363263$084.141649@attbi_s22...Show quote >I want to write a conditional statement in which a timestamp in a database >is compared to the current time. Example: If timestamp value is > >= (current time - 1 hour) then return 0, else return 1. > > The purpose of this is to verify that data has been imported in to the > table within the last hour. > > I figured the GetTime() function would provide me with the current time > but I'm not sure how to set it up so that it evaluates the current time - > 1 hour. > > Any help would be greatly appreciated. > > Thanks, > -- > Grant Smith > A+, Net+, MCP x 2 > > Quality Production Liaison > Hewlett Packard Company > > Database Administrator > Renaissance Systems and Services, LLC Mike Epprecht (SQL MVP) wrote:
> Hi Here's what I coded:> > SELECT DATEADD(hour, -1, GETDATE()) > CREATE PROCEDURE [dbo].[gs_VerifyImportLite] @timeCheck datetime = DateAdd(hour, -1, GetDate()) AS if exists (SELECT * FROM IMPORT_LITE WHERE import_date >= @timeCheck) return 0 else return 1 It keeps giving me syntax errors when I check syntax. Any ideas? -- Grant Smith A+, Net+, MCP x 2 Quality Production Liaison Hewlett Packard Company Database Administrator Renaissance Systems and Services, LLC CREATE PROCEDURE [dbo].[gs_VerifyImportLite]
AS DECALRE @timeCheck datetime SET @timeCheck = DateAdd(hour, -1, GetDate()) if exists (SELECT * FROM IMPORT_LITE WHERE import_date >= @timeCheck) return 0 else return 1 You can't declare and assign a value in one operation. This is not .NET Regards -- -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ "Grant Smith" <grant.sm***@envent-tech.com> wrote in message news:uBlif.582474$_o.422878@attbi_s71...Show quote > Mike Epprecht (SQL MVP) wrote: >> Hi >> >> SELECT DATEADD(hour, -1, GETDATE()) >> > > Here's what I coded: > > CREATE PROCEDURE [dbo].[gs_VerifyImportLite] > @timeCheck datetime = DateAdd(hour, -1, GetDate()) > > AS > if exists (SELECT * FROM IMPORT_LITE WHERE import_date >= @timeCheck) > return 0 > else > return 1 > > It keeps giving me syntax errors when I check syntax. > > Any ideas? > -- > Grant Smith > A+, Net+, MCP x 2 > > Quality Production Liaison > Hewlett Packard Company > > Database Administrator > Renaissance Systems and Services, LLC Mike Epprecht (SQL MVP) wrote:
Show quote > CREATE PROCEDURE [dbo].[gs_VerifyImportLite] I will give this a try. Thanks.> AS > > DECALRE @timeCheck datetime > > SET @timeCheck = DateAdd(hour, -1, GetDate()) > > if exists (SELECT * FROM IMPORT_LITE WHERE import_date >= @timeCheck) > return 0 > else > return 1 > > > You can't declare and assign a value in one operation. This is not .NET > > Regards > -- Grant Smith A+, Net+, MCP x 2 Quality Production Liaison Hewlett Packard Company Database Administrator Renaissance Systems and Services, LLC Grant Smith wrote:
Show quote > Mike Epprecht (SQL MVP) wrote: I actually had to code it like this to get the syntax check to recognize it:>> CREATE PROCEDURE [dbo].[gs_VerifyImportLite] >> AS >> >> DECALRE @timeCheck datetime >> >> SET @timeCheck = DateAdd(hour, -1, GetDate()) >> >> if exists (SELECT * FROM IMPORT_LITE WHERE import_date >= @timeCheck) >> return 0 >> else >> return 1 >> >> >> You can't declare and assign a value in one operation. This is not .NET >> >> Regards >> > > I will give this a try. Thanks. CREATE PROCEDURE [dbo].[gs_VerifyImportLite] @timeCheck datetime AS SET @timeCheck = DateAdd(hour, -1, GetDate()) if exists (SELECT * FROM IMPORT_LITE WHERE Upload_date >= @timeCheck) return 0 else return 1 According to what I've been reading, you have to declare variables before the AS section. Thanks again for your help. I just hope it works... LOL -- Grant Smith A+, Net+, MCP x 2 Quality Production Liaison Hewlett Packard Company Database Administrator Renaissance Systems and Services, LLC Hi
Anything before the AS is a parameter for the SP. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ "Grant Smith" <grant.sm***@envent-tech.com> wrote in message news:TQlif.576478$x96.119177@attbi_s72...Show quote > Grant Smith wrote: >> Mike Epprecht (SQL MVP) wrote: >>> CREATE PROCEDURE [dbo].[gs_VerifyImportLite] >>> AS >>> >>> DECALRE @timeCheck datetime >>> >>> SET @timeCheck = DateAdd(hour, -1, GetDate()) >>> >>> if exists (SELECT * FROM IMPORT_LITE WHERE import_date >= @timeCheck) >>> return 0 >>> else >>> return 1 >>> >>> >>> You can't declare and assign a value in one operation. This is not .NET >>> >>> Regards >>> >> >> I will give this a try. Thanks. > > I actually had to code it like this to get the syntax check to recognize > it: > > CREATE PROCEDURE [dbo].[gs_VerifyImportLite] > > @timeCheck datetime > > AS > > SET @timeCheck = DateAdd(hour, -1, GetDate()) > > if exists (SELECT * FROM IMPORT_LITE WHERE Upload_date >= @timeCheck) > return 0 > else > return 1 > > According to what I've been reading, you have to declare variables before > the AS section. Thanks again for your help. I just hope it works... LOL > -- > Grant Smith > A+, Net+, MCP x 2 > > Quality Production Liaison > Hewlett Packard Company > > Database Administrator > Renaissance Systems and Services, LLC Mike Epprecht (SQL MVP) wrote:
Show quote > Hi OK. Now I understand. Thanks again.> > Anything before the AS is a parameter for the SP. > > Regards > -------------------------------- > Mike Epprecht, Microsoft SQL Server MVP > Zurich, Switzerland > > IM: m***@epprecht.net > > MVP Program: http://www.microsoft.com/mvp > > Blog: http://www.msmvps.com/epprecht/ > > "Grant Smith" <grant.sm***@envent-tech.com> wrote in message > news:TQlif.576478$x96.119177@attbi_s72... >> Grant Smith wrote: >>> Mike Epprecht (SQL MVP) wrote: >>>> CREATE PROCEDURE [dbo].[gs_VerifyImportLite] >>>> AS >>>> >>>> DECALRE @timeCheck datetime >>>> >>>> SET @timeCheck = DateAdd(hour, -1, GetDate()) >>>> >>>> if exists (SELECT * FROM IMPORT_LITE WHERE import_date >= @timeCheck) >>>> return 0 >>>> else >>>> return 1 >>>> >>>> >>>> You can't declare and assign a value in one operation. This is not .NET >>>> >>>> Regards >>>> >>> I will give this a try. Thanks. >> I actually had to code it like this to get the syntax check to recognize >> it: >> >> CREATE PROCEDURE [dbo].[gs_VerifyImportLite] >> >> @timeCheck datetime >> >> AS >> >> SET @timeCheck = DateAdd(hour, -1, GetDate()) >> >> if exists (SELECT * FROM IMPORT_LITE WHERE Upload_date >= @timeCheck) >> return 0 >> else >> return 1 >> >> According to what I've been reading, you have to declare variables before >> the AS section. Thanks again for your help. I just hope it works... LOL >> -- >> Grant Smith >> A+, Net+, MCP x 2 >> >> Quality Production Liaison >> Hewlett Packard Company >> >> Database Administrator >> Renaissance Systems and Services, LLC > > -- Grant Smith A+, Net+, MCP x 2 Quality Production Liaison Hewlett Packard Company Database Administrator Renaissance Systems and Services, LLC Grant Smith (grant.sm***@envent-tech.com) writes:
> I want to write a conditional statement in which a timestamp in a Since there is no GetTime() function in SQL Server, this is a little> database is compared to the current time. Example: If timestamp value is > >= (current time - 1 hour) then return 0, else return 1. > > The purpose of this is to verify that data has been imported in to the > table within the last hour. > > I figured the GetTime() function would provide me with the current time > but I'm not sure how to set it up so that it evaluates the current time > - 1 hour. confusing. In which environment are you working in and want to write you conditional statement? -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland Sommarskog wrote:
Show quote > Grant Smith (grant.sm***@envent-tech.com) writes: I'm sorry. I meant to say GetDate()>> I want to write a conditional statement in which a timestamp in a >> database is compared to the current time. Example: If timestamp value is >>> = (current time - 1 hour) then return 0, else return 1. >> The purpose of this is to verify that data has been imported in to the >> table within the last hour. >> >> I figured the GetTime() function would provide me with the current time >> but I'm not sure how to set it up so that it evaluates the current time >> - 1 hour. > > Since there is no GetTime() function in SQL Server, this is a little > confusing. In which environment are you working in and want to write > you conditional statement? > -- Grant Smith A+, Net+, MCP x 2 Quality Production Liaison Hewlett Packard Company Database Administrator Renaissance Systems and Services, LLC Grant Smith wrote:
Show quote > I want to write a conditional statement in which a timestamp in a OK... So I wrote the following stored procedure and it runs just fine > database is compared to the current time. Example: If timestamp value is > >= (current time - 1 hour) then return 0, else return 1. > > The purpose of this is to verify that data has been imported in to the > table within the last hour. > > I figured the GetTime() function would provide me with the current time > but I'm not sure how to set it up so that it evaluates the current time > - 1 hour. > > Any help would be greatly appreciated. > > Thanks, but I can't seem to get the job to fail on a return of 1. Any ideas? CREATE PROCEDURE [dbo].[gs_VerifyImportLite] AS DECLARE @timeCheck datetime SET @timeCheck = DateAdd(hour, -1, GetDate()) if exists (SELECT * FROM IMPORT_LITE WHERE Upload_date >= @timeCheck) return 0 else return 1 GO Thanks, -- Grant Smith A+, Net+, MCP x 2 Quality Production Liaison Hewlett Packard Company Database Administrator Renaissance Systems and Services, LLC Grant Smith (grant.sm***@envent-tech.com) writes:
> OK... So I wrote the following stored procedure and it runs just fine Did you explain what context the program runs in? Without knowledge of> but I can't seem to get the job to fail on a return of 1. Any ideas? that, it's difficult to have ideas. :-) But if you want an error, using RAISERROR may be a better option. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Erland Sommarskog wrote:
> Grant Smith (grant.sm***@envent-tech.com) writes: It is a Stored Procedure running as a SQL Agent Job. I changed the SP to >> OK... So I wrote the following stored procedure and it runs just fine >> but I can't seem to get the job to fail on a return of 1. Any ideas? > > Did you explain what context the program runs in? Without knowledge of > that, it's difficult to have ideas. :-) > > But if you want an error, using RAISERROR may be a better option. > > > use RAISERROR. I haven't figured out if it works yet or not because there hasn't been a situation where I could force a fail... LOL Thanks, -- Grant Smith A+, Net+, MCP x 2 Quality Production Liaison Hewlett Packard Company Database Administrator Renaissance Systems and Services, LLC  |
|||||||||||||||||||||||
Other interesting topics