need sloution for current date in a job

Question

need sloution for current date in a job

Krissh_pallu

SSC Enthusiast

Points: 109
More actions
August 23, 2012 at 1:25 am

#266395

My job starts at 8:00pm of currrent day to 3:00am of next day.
This job has serveral steps
for each step i need the current date.
if it meets 12:00 mid night the current date will change to next day.
but i need the 8:00 pm in all the steps upto that jobends
pls help me..........asap

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

declare a global variable and update the value in that variable at start of the package. Use the same variable in all steps.

----------
Ashish

Krissh_pallu SSC Enthusiast Points: 109 More actions · Answer 2

Thanks for your response.........

here the job is a simple job created in sql agent...

Is there any chance to use a global variable inside a job for all steps

crazy4sql SSCoach Points: 19590 More actions · Answer 3

not sure what kind of job is that and how many steps in it but as all the job and steps will be in msdb database, you can create one local variable in msdb and use it across the steps.

Something like

declare @date datetime

set @date = GETDATE()

print @date

----------
Ashish

Krissh_pallu SSC Enthusiast Points: 109 More actions · Answer 4

thanks again...

This Job has nearly 10 steps...

In each step I am parsing the date to my stored prcoedure.

Based on this date it will generate a report..

...

I got your idea.....

I have a question for your answer

Will it be possible to use the same variable for all the remaining steps..

happycat59 One Orange Chip Points: 29387 More actions · Answer 5

Have a look at the topic "Using Tokens in Job Steps" in Books Online. SQL Server Agent has a number of tokens that can be used that give you some information about the environment and job that is being run. One of these (STRTDT) is the date that the job started.

OTF SSCertifiable Points: 6054 More actions · Answer 6

Krissh_pallu (8/23/2012)
thanks again...
This Job has nearly 10 steps...
In each step I am parsing the date to my stored prcoedure.
Based on this date it will generate a report..
...
I got your idea.....
I have a question for your answer
Will it be possible to use the same variable for all the remaining steps..

You have been given two possible solutions to your problem here. I would try them and see which suits although the Sql Agent Token solution comes across as a more elegant one.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 7

If all of your steps are TSQL steps, you could record the job start time in a table in the first step, and then each step could do a lookup against that table to get that time.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Something like the following be helpful?

declare @TestDate datetime = getdate();

select @TestDate, dateadd(hh, 20, dateadd(dd, datediff(dd, 0, dateadd(hh, -20, @TestDate)), 0));

set @TestDate = '2012-08-24 20:00:00.000';

select @TestDate, dateadd(hh, 20, dateadd(dd, datediff(dd, 0, dateadd(hh, -20, @TestDate)), 0));

set @TestDate = '2012-08-24 21:00:00.000';

select @TestDate, dateadd(hh, 20, dateadd(dd, datediff(dd, 0, dateadd(hh, -20, @TestDate)), 0));

set @TestDate = '2012-08-25 01:00:00.000';

select @TestDate, dateadd(hh, 20, dateadd(dd, datediff(dd, 0, dateadd(hh, -20, @TestDate)), 0));