x

Adjusting GetDate() in agent job.

I have a job to change the Status(Int) from 1 to 0 when the Time in the Time colum(Datetime) matches the current time .Now my T-sql job runs every 10 minutes checking for records that have status 1 .I have written this query in the but if fails to fire.

 Update Status set status=0  where status=1 and Time=getdate()
more ▼

asked Sep 16, 2012 at 11:11 AM in Default

fashraf gravatar image

fashraf
428 13 16 21

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

You may need to review the data in the Time column and make sure that there will be a match. With the query as you have written it you would only update row that exactly match, not the nearest 10 minutes, not the nearest minute, not necessarily even the nearest second. If your Time column has millisecond accuracy then the GetDate() value will have to match to that accuracy.

What datatype is your Time column?

eg:

USE [CentralDB]
GO

DECLARE @Times TABLE
(
  TimeCol DATETIME ,
  RowData VARCHAR(25)
)

INSERT @Times
( [TimeCol], [RowData] )
VALUES ( '2012-09-17 07:06:31', -- TimeCol - datetime
  'Larry'  -- RowData - varchar(25)
  ),
( '2012-09-17 07:06:31.25', -- TimeCol - datetime
  'Curly'  -- RowData - varchar(25)
  ),
( '2012-09-17 07:06:35', -- TimeCol - datetime
  'Mo'  -- RowData - varchar(25)
  ),
( '2012-09-17 07:06:31', -- TimeCol - datetime
  'Groucho'  -- RowData - varchar(25)
  ),
( '2012-09-17 07:06:31', -- TimeCol - datetime
  'Zeppo'  -- RowData - varchar(25)
 ),
( '2012-09-17 07:06:45', -- TimeCol - datetime
  'Harpo'  -- RowData - varchar(25)
  ),
( '2012-09-17 07:07:31', -- TimeCol - datetime
  'Chico'  -- RowData - varchar(25)
  )

SELECT [t].[TimeCol] ,
[t].[RowData] ,
GETDATE() AS GetDate_Val
FROM @Times AS t
Now, if you want to catch all the rows that are in a range of times, or happened since a certain time then you will need to use >=, or specify the range you need.
more ▼

answered Sep 17, 2012 at 07:11 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left

Does it fail to fire or does it fail to update the row? Those I believe are two different things. Any error messages?

Your logic might also be off as the rows that exist were created before now (which is what getdate() will return) thus I don't think that your update query will update any rows ever.

Hope this helps!
more ▼

answered Sep 16, 2012 at 01:45 PM

JohnM gravatar image

JohnM
6.9k 1 3 7

well it fires but it does not execute..and the Time column in the table refers to the time its supposed to be activated ..for example i set a Time of 2:00 pm and when the job runs at 2:00pm it runs but does not change the status coz it checks the seconds and milliseconds and fails
Sep 16, 2012 at 02:09 PM fashraf
Ah got it. How is that column defined? As a datetime?
Sep 16, 2012 at 02:15 PM JohnM
yes sir..its Datetime
Sep 16, 2012 at 03:30 PM fashraf

You could do something like this:

DECLARE @myDate DATETIME = '2012-09-16 20:52'

IF @myDate = LEFT(GETDATE(), 20) BEGIN PRINT 'Success' END ELSE BEGIN PRINT 'Failed!' END GO

Depending on the data set size, I'd recommend testing this as well as looking at the execution plan to confirm that it doesn't introduce other issues.

Hope this helps!
Sep 17, 2012 at 01:55 AM JohnM
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1853
x87

asked: Sep 16, 2012 at 11:11 AM

Seen: 941 times

Last Updated: Sep 17, 2012 at 07:12 AM