Finding latest time setting
So we have jobs that happen anywhere from 6PM to 6AM in each database. This setting may be used on multiple customers on each database. I'm just looking for a way to find the latest time on each database. I always want the latest time in the night. So if one job is set in the db as 23:00:00 and other is set to 01:00:00 I would want the '01:00:00' returned.
Example times:
'19:10:00'
'21:00:00'
'02:00:00'
'04:00:00'
Expected result will be the '04:00:00'. There could be 1-5 rows of times returned depending on the db.
I've tried to play with group by and playing with max and min a bit, but it was getting ugly, since in my example it would take 02:00:00 as min and max at 21:00:00 and wouldn't really validate the 04:00:00. So just looking for some more ideas.
Thank you!
Posted Feb 18, 2019, 7:53 PM UTC
I’m sure you can do better - like for example, just using DATEADD(‘hours’, -18, Number). I’m on mobile.
Another hack solution considered converting them from 6pm-6am and assigning them an integer 0-24?
For example, just do the number - 18. So 6pm = 0, 24 = 6, and 4am = 10.
You would accomplish this somehow like so:
ABS(Number - 18)
Ah that is a good way to look at it. I kept trying to play with DATEDIFF from 6PM. But obviously 1AM wouldn't show up as 7 like you would hope(Of course it was the right answer it returned, just not what you hope for haha).
What kind of database? For example, you can do this on Oracle:
On others:
From there, I assume you want to only get the time out of it: https://blog.sqlauthority.com/2009/08/06/sql-server-get-time-in-hourminute-format-from-a-datetime-get-date-part-only-from-datetime/amp/
Yeah MS. I'm starting to play with that.
I’m on Mobile, but still trying for you: