November 10, 2015 at 7:49 am
Even more readable... WHERE js.next_run_date = CONVERT(VARCHAR(8), GETDATE(), 112)
The outer cast is unnecessary as datetime will equate with a valid date in string format (e.g., = '20151110')
November 10, 2015 at 7:49 am
Our main software is a third party solution with a legacy database that stores all it dates in a format similar to that: CYYMMDD, where C = Century = 1. It is such a royal pain to query this database. Every time you need a filter by date you need to do this: 1000000 + ( YEAR(GETDATE()) - 2000 ) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE()).
What really gets me is the storage size is for datetime is 8 bytes, so for date-only fields they are saving a mere 1 byte, but for fields where they also have to tack on an extra time field (which is also decimal, hhmmss) they are actually losing 5 bytes.
Be still, and know that I am God - Psalm 46:10
November 10, 2015 at 7:53 am
david.gugg (11/10/2015)
Our main software is a third party solution with a legacy database that stores all it dates in a format similar to that: CYYMMDD, where C = Century = 1. It is such a royal pain to query this database. Every time you need a filter by date you need to do this: 1000000 + ( YEAR(GETDATE()) - 2000 ) * 10000 + MONTH(GETDATE()) * 100 + DAY(GETDATE()).What really gets me is the storage size is for datetime is 8 bytes, so for date-only fields they are saving a mere 1 byte, but for fields where they also have to tack on an extra time field (which is also decimal, hhmmss) they are actually losing 5 bytes.
And if you consider that you can have a date type for 3 bytes, you're losing a lot of space.
November 10, 2015 at 7:57 am
Ed Wagner (11/10/2015)
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
aroundwith it. 😉
Same here. Just another of those "quirks" with sql server that you only know by experience. 😉
_______________________________________________________________
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 8:17 am
Here's another approach: convert the integers for run_date and run_time to an actual DATETIME value, then compare using a standard date-time clause. Warning, functions in WHERE clause predicate. Jeff, please put down the pork chop launcher. 🙂
SELECT TOP 10 *
FROM
dbo.sysjobschedules js
INNER JOIN dbo.sysjobs AS s ON s.job_id = js.job_id
WHERE
js.next_run_time <> 0 AND
dbo.agent_datetime(js.next_run_date, js.next_run_time) >= DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0) AND
dbo.agent_datetime(js.next_run_date, js.next_run_time) < DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP) + 1, 0);
I learned about the msdb function agent_datetime() here.
Rich
November 10, 2015 at 8:23 am
webrunner (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.I knew that this was the case but still don't get it. Why is this data type used for job schedules?
Thanks,
webrunner
Simply because sysjobschedules is older than the DATE data type, by a wide margin. Pathetically enough, DATE was introduced in SQL Server 2008, whereas sysjobschedules was introduced in SQL 2005.
November 10, 2015 at 8:36 am
dario.quinto (11/10/2015)
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
Wha? More readable? It relies on a magic number. If you know what 112 means, that's great, but I'd argue this is really not readable.
FORMAT is better if you use 2012 +
November 10, 2015 at 8:57 am
Ed Wagner (11/10/2015)
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.
Oh now I see it. The table in msdb >> System Tables. I looked in master and didn't see it. Thanks for the reply.
Ed Wagner (11/10/2015)
The QOTD is all about learning something new.
Thanks for clearing that up. I was thinking the QOTD was just to make me feel dumb. :rolleyes: (sarcasm)
November 10, 2015 at 10:50 am
Dain Bramage (11/10/2015)
Ed Wagner (11/10/2015)
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.
Oh now I see it. The table in msdb >> System Tables. I looked in master and didn't see it. Thanks for the reply.
Ed Wagner (11/10/2015)
The QOTD is all about learning something new.Thanks for clearing that up. I was thinking the QOTD was just to make me feel dumb. :rolleyes: (sarcasm)
I think it makes most of us feel dumb at one time or another.
November 11, 2015 at 11:32 pm
Where this may not return a result though would be where a new job has been set up, it has not run for the first time (it is scheduled to run later) and the sysjobschedules table has not been refreshed and so the job has no next run date.
Also I find the following works:
SELECT top 10
*
FROM dbo.sysjobschedules js
INNER JOIN dbo.sysjobs AS s
ON s.job_id = js.job_id
WHERE js.next_run_date > CAST(GETDATE() AS INT);
...
November 12, 2015 at 1:57 am
Nice one, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 18, 2015 at 2:27 am
Thanks for this interesting question.
I knew the "special" format YYYYMMDD as I am curious and I have studied the sysjobschedules table. The format for the date and time is maybe surprising but logical ( I have worked in a factory during the 1970 years and it was a current habit ).
For the way to convert date and time , I am choosing always this one which is executing the quickest executing way as soon as I am sure that the result is correct...
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply