How do I validate 15 minute intervals using SQL

I have data stored in minutes in a column called 'MYTIME'.  The values can range from 1 to 1440 but I have one customer who wants to validate that that the value entered is in a 15 minute interval.  
So the values 15, 30, 45, 60, 75, 90, 105 etc, etc up tp 1440 would all be valid.

Is there a simple SQL that I can run to validate the column has the right interval?

Thanks
hmstechsupportAsked:
Who is Participating?
 
hmstechsupportAuthor Commented:
After doing a little checking wouldn't this work?

SELECT MYTIME FROM MYTABLE WHERE MYTIME % 15 <> 0
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have several options:
* store all the possible values in a "lookup table", and make the field a foreign key to that table
* make a check constraint that mytime % 15 = 0


0
 
knightEknightCommented:
This will pull the rows where the time is not one of those values:


; with cte as (
  select 0 as interval
  union all
  select interval+15
  from cte
  where interval < 1440
)

select *
from myTable
where MYTIME not in (select interval from cte)
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Anthony PerkinsCommented:
It really depends on your definition of "validate".  If you post in this zone, we assume you want a database constraint and that is what angelIII gave you here http:#a35335381.  But it sounds like what you really are looking for is a presentation level validation, if so than you are correct.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I confirm that visibly you wanted to query for those that don't match, and not yet set up a method to avoid (future) entries to confirm to the "constraint".
0
 
hmstechsupportAuthor Commented:
Thanks to all of you, and my apologies if the question was not clear enough or posted in the wrong zone.
0
 
hmstechsupportAuthor Commented:
The answer I was looking for was devised by me after looking in other sites.
The answers given may be accurate but they do not address my question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.