Job Schedules

Question

Post reply

Job Schedules

Steve Jones - SSC Editor

SSC Guru

Points: 736478
More actions
November 9, 2015 at 8:48 pm

#72563

Comments posted to this topic are about the item Job Schedules
November 9, 2015 at 10:33 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 26 total)

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

Hany Helmy SSChampion Points: 13921 More actions · Answer 1

Nice one, good 2 know this, learned somthin new 2day, thanx.

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

iain.jacob 29849 Grasshopper Points: 23 More actions · Answer 2

There is always another way...

WHERE js.next_run_date = cast(convert(varchar(8),GETDATE(),112) as int);

Thom A SSC Guru Points: 99133 More actions · Answer 3

You could also use format (as below), if using SQL Server 2012 or later.

where js.next_run_date = cast(format(GETDATE(), 'yyyyMMdd') as int)

Cheers!

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

dario.quinto SSC Journeyman Points: 90 More actions · Answer 4

iain.jacob 29849 (11/10/2015)
There is always another way...
WHERE js.next_run_date = cast(convert(varchar(8),GETDATE(),112) as int);

yep, and personally I find this more readable

Dain Bramage Valued Member Points: 70 More actions · Answer 5

If I would have known that the date stored in dbo.sysjobschedules is an integer that is calculated to be the year, month, and day, as a large value then I would have got this right. How was I to know that though?

Ed Wagner SSC Guru Points: 287026 More actions · Answer 6

Dain Bramage (11/10/2015)
If I would have known that the date stored in dbo.sysjobschedules is an integer that is calculated to be the year, month, and day, as a large value then I would have got this right. How was I to know that though?

That's what the question is about. If you've ever queried the job schedules table, you would have had to deal with it.

Now that you know about it, take a look at the jobs and job schedules tables. The QOTD is all about learning something new.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Larnu (11/10/2015)
You could also use format (as below), if using SQL Server 2012 or later.
where js.next_run_date = cast(format(GETDATE(), 'yyyyMMdd') as int)
Cheers!

Knowing format is several times slower than any other alternative, I try to forget about it for everything.:-D

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

I had to check the table structure of sysjobschedules. I would never have guessed that a date column was put into a purposely constructed int.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 9

iain.jacob 29849 (11/10/2015)
There is always another way...
WHERE js.next_run_date = cast(convert(varchar(8),GETDATE(),112) as int);

I'll just drop the var part as you'll never have a different length.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 10

iain.jacob 29849 (11/10/2015)
There is always another way...
WHERE js.next_run_date = cast(convert(varchar(8),GETDATE(),112) as int);

Now that's the answer I was looking for.

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Iwas Bornready (11/10/2015)
I had to check the table structure of sysjobschedules. I would never have guessed that a date column was put into a purposely constructed int.

For some reason MS has a habit of doing that. I find it incredibly frustrating as it flies in the face of industry best practices about using the proper datatype for the data being stored. I also understand why they do it, because it makes doing math against those values a lot easier.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ed Wagner SSC Guru Points: 287026 More actions · Answer 12

Sean Lange (11/10/2015)
Iwas Bornready (11/10/2015)
I had to check the table structure of sysjobschedules. I would never have guessed that a date column was put into a purposely constructed int.
For some reason MS has a habit of doing that. I find it incredibly frustrating as it flies in the face of industry best practices about using the proper datatype for the data being stored. I also understand why they do it, because it makes doing math against those values a lot easier.

It does fly in the face of industry norms, but it's been that way for years. Why would they ever change it?

To be fair, fixing it would break an awful lot of stuff all over the world during an upgrade, so I see why they leave it like it is. We've all found ways to work ~~around~~ with it. 😉

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

webrunner SSC-Dedicated Points: 31882 More actions · Answer 13

Iwas Bornready (11/10/2015)
I had to check the table structure of sysjobschedules. I would never have guessed that a date column was put into a purposely constructed int.

I knew that this was the case but still don't get it. Why is this data type used for job schedules?

Thanks,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html