Post reply

Job Schedules

  • 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

  • November 9, 2015 at 10:58 pm

    #1838749

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

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • November 10, 2015 at 12:53 am

    #1838757

    There is always another way...

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

  • November 10, 2015 at 2:31 am

    #1838775

    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

  • November 10, 2015 at 3:38 am

    #1838786

    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

  • November 10, 2015 at 6:06 am

    #1838809

    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?

  • November 10, 2015 at 6:20 am

    #1838813

    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

  • November 10, 2015 at 6:38 am

    #1838823

    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

  • November 10, 2015 at 6:43 am

    #1838826

    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.

  • November 10, 2015 at 6:43 am

    #1838827

    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

  • November 10, 2015 at 6:44 am

    #1838828

    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.

  • November 10, 2015 at 7:36 am

    #1838846

    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/

  • November 10, 2015 at 7:42 am

    #1838848

    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

  • November 10, 2015 at 7:45 am

    #1838849

    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