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 '12 at 11:11 AM in Default

fashraf gravatar image

fashraf
398 12 13 18

(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 '12 at 07:11 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

(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 '12 at 01:45 PM

JohnM gravatar image

JohnM
6k 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 '12 at 02:09 PM fashraf
Ah got it. How is that column defined? As a datetime?
Sep 16 '12 at 02:15 PM JohnM
yes sir..its Datetime
Sep 16 '12 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 '12 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1816
x85

asked: Sep 16 '12 at 11:11 AM

Seen: 764 times

Last Updated: Sep 17 '12 at 07:12 AM