CODEWITHSUNDEEP
sql-serversql-server-2008
Rita
Rita

Reputation: 1237

What is the Datatype for 10:00 AM PST in SQL Server

I have a table that need to store Timeslot such as 10:00 AM PST, 10:15 AM PST.... etc at a constant interval of 15 Min.

I am wondering if there is any Time datatype available for this kind of data?

I need to store them as 11:00 AM PST, 11:15 AM PST, 11:30 AM PST, 11:45 PM PST, 12:00 PM PST, 12:15 PM PST, 12:30 PST, 12:45 PM PST, 01:00 PM PST..... etc.

Also in future if the business requirement is for 20 min interval, I should be able to change it easily.

Thanks

Upvotes: 3

Views: 527

Answers (3)

Jeff Sternal
Jeff Sternal

Reputation: 48593

You could use the Time data type.

If that's overkill for you since you only need to store the interval at the level of minutes, you could store the minutes since midnight, within the range 0-1339.

You might even consider storing the number of 15-minute intervals since midnight, such that 2:00 AM is 8 and 2:15 AM is 9 (though unless you can think of a great name for such a column, it wouldn't be very clear).

Upvotes: 3

Daniel DiPaolo
Daniel DiPaolo

Reputation: 56390

If you're looking to constrain a time to 15 minute intervals, the easiest way to do it would be to just store it in an int constrained between 0-100 (some daylight savings days have 25 hours) and calculate when the exact time is if you need it. I've worked on a few large applications that have used this for stuff like weather forecasting data and it has worked quite well.

Upvotes: 1

Oded
Oded

Reputation: 499012

There is a time data type you can use (SQL Server 2008).

CREATE TABLE Table1 ( Column1 time(7) )

The range is:

00:00:00.0000000 through 23:59:59.9999999

You can use a CHECK CONSTRAINT to ensure that the minutes part is one of (0, 15, 30, 45).

Upvotes: 3

Related Questions