GETDATE() in a T-SQL Job Step Command

Question

I can't seem to get the GETDATE() syntax to work in a Job Step of type Transact-Sql Script. I put in the command as:

execute insertMostRecentUpdate 
@Data='Data', 
@Date=GETDATE()-1

But I get an "incorrect syntax near ')'" error when parsing or trying to run it. Any thoughts?

score 7 · Accepted Answer · edited Dec 12 '11 at 19:19

7

Try this:

DECLARE @date DATETIME;
SET @date = GETDATE()-1;

execute insertMostRecentUpdate 
@Data='Data', 
@Date=@date;

You cannot use GETDATE() as inline-function while calling a procedure.

edited Dec 12 '11 at 19:19

answered Jul 22 '10 at 12:09

Florian Reischl

In addition to Florian: Be careful that getdate()-1 does not mean yesterday (as getdate has a time component as well) – Tobiasopdenbrouw Jul 22 '10 at 12:11
Thanks Flo! Learn something new every day. I'm a little annoyed that none of my googling turned this up :) – C Bauer Jul 22 '10 at 12:12
1

@C Bauer Very welcome :-); and please also notice @Tobiasopdenbrouw's very important information. As a second extension: With SS2k8 we all should start using DATETIME2 and SYSDATETIME() and get rid of DATETIME and GETDATE(). – Florian Reischl Jul 22 '10 at 12:19
GETDATE()-1 is yesterday, that's what I'm using it for :D In 2k8 they are phasing out GETDATE() and DATETIME? DATETIME2 is a bit ugly :/ – C Bauer Jul 22 '10 at 12:24
DATETIME (and all related functions) does have a bug. It's imprecise with milliseconds that can cause issues. Try this: SELECT CAST('2010-01-01 23:59:59.999' AS DATETIME), CAST('2010-01-01 23:59:59.999' AS DATETIME2); DATETIME2 is much more precise (100ns) – Florian Reischl Jul 22 '10 at 12:33
1

@CBauer - no, Getdate()-1 is a specific date AND time (yesterday), and not the whole period that is 'yesterday'. As you're inserting, you're probably not bothered by it, but careful when selecting. – Tobiasopdenbrouw Jul 22 '10 at 12:50

score 1 · Answer 2 · answered Jul 22 '10 at 12:18

1

You could try something like this,

declare @date date
set @date = GETDATE()-1

exec insertMostRecentUpdate 'data',@date

Suprise me when i ran, thought i should compile, but I think its because you are passing a function into your proc

answered Jul 22 '10 at 12:18

Iain

2 Answers2