November 9, 2015 at 8:48 pm
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
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
There is always another way...
WHERE js.next_run_date = cast(convert(varchar(8),GETDATE(),112) as int);
November 10, 2015 at 3:38 am
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
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
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.
November 10, 2015 at 6:38 am
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
November 10, 2015 at 6:43 am
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
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.
November 10, 2015 at 6:44 am
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
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
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. 😉
November 10, 2015 at 7:45 am
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