SQL Data Types: DATE, TIME & TIMESTAMP

Instructor: Kaitlin Oglesby
From keeping track of changes to recording dates, SQL has some features built into its data types that not only help make sure that times and dates are properly formatted, but also that records are kept free from any alterations. In this lesson, we'll learn about the DATE, TIME and TIMESTAMP data types.

Knowing When in SQL

Let's face it: knowing the times associated with your data is almost as useful as knowing the data you're working with. For example, if you were making a list of customers to call to wish a pleasant Thanksgiving to, it would be a little odd if you were to call customers who haven't done business with your company in a decade. To avoid such irrelevancies, many databases find it useful to record dates and times in their records. In this lesson, we'll look at the data types of DATE, TIME, and TIMESTAMP.

Why Use a Specific Data Type?

You may be thinking 'why do I have to keep track of these new types?' Couldn't you just use a different data type, like INTEGER or TEXT? You're absolutely right - you could. However, databases have a lot of information in them, and anything we can do to make sure that the information we need is as accurate as possible helps us in the long run. Therefore, the ability to have SQL instantly know that the value needed is a date or a time is a major help.

The TIME Data Type

We'll start with the TIME data type. This is useful for a number of reasons. Perhaps you use SQL to keep track of meetings, or to record when a certain employee should move on to a different task. For whatever reason, it is relatively straightforward to use TIME. Here is an example:

worker_time TIME,

The column 'worker_time' is the name of the category, while TIME tells it that it should be in a format of HH:MM:SS.[nnnnnnn]. H stands for hour, M stands for minute, S stands for second, and all those ns are for fractions of a second. To type that in, by the way, it would just be one string of integers, so 061530 would be 6:15:30.

The DATE Data Type

TIME has its usage, but chances are you'll be using DATE much more often. After all, it lets you record the date of a particular transaction, meeting, sales call, or just about anything else you can imagine. Here's an example of it in an SQL query:

contract_date DATE,

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support