Best way to store time (hh:mm) in a database

sql-server database database-design datetime
Matthew Dresser picture Matthew Dresser · Feb 11, 2009 · Viewed 138.3k times · Source

I want to store times in a database table but only need to store the hours and minutes. I know I could just use DATETIME and ignore the other components of the date, but what's the best way to do this without storing more info than I actually need?

Answer

BigJump picture BigJump · Feb 11, 2009

You could store it as an integer of the number of minutes past midnight:

eg. 

0 = 00:00 
60 = 01:00
252 = 04:12

You would however need to write some code to reconstitute the time, but that shouldn't be tricky.

Related questions

What does ON [PRIMARY] mean?

I'm creating an SQL setup script and I'm using someone else's script as an example. Here's an example of the script: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[be_Categories]( [CategoryID] [uniqueidentifier] ROWGUIDCOL NOT …

Read More
What columns generally make good indexes?

As a follow up to "What are indexes and how can I use them to optimise queries in my database?" where I am attempting to learn about indexes, what columns are good index candidates? Specifically for an MS SQL database? …

Read More
Relationship of Primary Key and Clustered Index

Can a TABLE have a primary key without a clustered index? And can a TABLE have a clustered index without having a primary key? Can anybody briefly tell me the relationship between primary key and clustered index?

Read More