Job Schedules

  • Comments posted to this topic are about the item Job Schedules

  • This was removed by the editor as SPAM

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

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • There is always another way...

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

  • 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

  • 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

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

  • 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.

  • 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
  • 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.

  • 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
  • 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.

  • 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/

  • 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. 😉

  • 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

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

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