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

avatar image

538 17 22 29

(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?


 USE [CentralDB]

   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

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(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

avatar image

14.4k 3 7 15

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'


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



Answers and Comments

SQL Server Central

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



asked: Sep 16, 2012 at 11:11 AM

Seen: 1327 times

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

Copyright 2018 Redgate Software. Privacy Policy