intro

Let's learn everything you need to know about date management in a database by exploring the most popular SQL date data types.

Tools used in the tutorial

DBVISUALIZER

TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

DOWNLOAD

Popular database technologies provide many SQL date data types, including DATE, TIME, DATETIME, and TIMESTAMP. If you have ever wondered about the differences between these data types and how to use them, you have come to the right place!

In this article, you will look at the different date SQL data types, exploring their operators and functions in different database technologies.

Let's dive in!

SQL Date Data Types

Each database technology provides different SQL date data types. Let’s explore the most important ones categorized by DBMS.

MySQL

DATE: Stores a date value in the format 'YYYY-MM-DD', representing year, month, and day.
TIME: Stores a time value in the format 'hh:mm:ss', representing hours, minutes, and seconds.
DATETIME: Combines DATE and TIME, storing both date and time components.
TIMESTAMP: Similar to DATETIME but it automatically converts the stored time to UTC before storage and converts it back to the session time zone upon retrieval.

More information about MySQL date data types can be found in the documentation.

PostgreSQL

DATE: Stores a date in the format 'YYYY-MM-DD', with a year, month, and day.
TIME: Stores a time value, can adhere to a timezone.
TIMESTAMP: Stores a timestamp value, can adhere to a timezone.
INTERVAL: Stores a time interval.

Learn more in the official documentation.

SQL Server

DATE: Stores a date value in the format 'YYYY-MM-DD'.
TIME: Stores a time value in the format 'hh:mm:ss'.
SMALLDATETIME: Stores a date that is combined with time without fractional seconds.
DATETIME: Stores a date and time value with fractional seconds accuracy, up to 3.33 milliseconds.
DATETIME2: Enhanced version of DATETIME, with higher fractional seconds accuracy (up to 100 nanoseconds).
DATETIMEOFFSET: Stores a date and time value with timezone offset information, ensuring accurate representation across different time zones.

Check out the documentation for more information.

Oracle

DATE: Stores an SQL date value with an optional time component.
TIMESTAMP: Stores a datetime value with fractional seconds accuracy, up to 9 digits.
TIMESTAMP WITH TIME ZONE: Stores a datetime value with information related to a timezone, ensuring accurate representation across different time zones.
TIMESTAMP WITH LOCAL TIME ZONE: Stores a datetime value in the database's local time zone, automatically converting time zones as needed.

Explore the documentation for more details.

DATE vs TIME vs DATETIME vs TIMESTAMP in SQL

Time to better understand the main differences between the three main SQL date data types!

DATE

The DATE SQL data type stores date values only, without any time components. It is useful for representing calendar dates.

Example: '2024-04-15'.

Date value in DbVisualizer

Ideal for: Storing the date values without any time information, such as birthdates or deadlines.

TIME

The TIME SQL data time stores time values only, without any date components.

Example: '13:45:30'.

Time value in DbVisualizer

Ideal for: Storing time values without any date information, such as opening hours.

DATETIME

The DATETIME stores both date and time values, allowing for more precise representations of moments in time.

Example: '2024-01-17 18:32:31'.

Ideal for: Storing both date and time information, such as the start date and time of an event.

TIMESTAMP

Similar to DATETIME, but it can also include timezone information. In detail, the TIMESTAMP SQL data type stores the number of seconds since the Unix epoch (January 1, 1970).

Example: '2024-01-17 18:32:31'.

Timestamp value in DbVisualizer

Ideal for: Storing information to keep track of changes made to records in a database (e.g., created_at and updated_at columns).

SQL Date Functions You Need to Know

Various database engines support several SQL date functions to perform operations on their datetime data types. Explore them all, listed by DBMS.

MySQL

ADDDATE(): Add time values (intervals) to a date value. Dig into this function in our SQL add to date guide.
ADDTIME(): Add time to a datetime value.
CONVERT_TZ(): Convert from one time zone to another.
DATE(): Extract the date part of a date or datetime expression.
DATE_ADD(): Add time values (intervals) to a date value.
DATE_FORMAT(): Format the given date as specified.
DATE_SUB(): Subtract a time value (interval) from a date. When invoked with three arguments, SUBDATE() is its synonym.
DATEDIFF(): Subtract two dates.
DAYNAME(): Return the name of the weekday
DAYOFMONTH(): Return the day of the month (0-31) from a specified date. An equivalent synonym is DAY().
DAYOFWEEK(): Return the weekday index of the argument.
DAYOFYEAR(): Return the day of the year (1-366) from a given date.
EXTRACT(): Extract a part of a date.
FROM_DAYS(): Convert a day number to a date.
FROM_UNIXTIME(): Format a Unix timestamp as a date.
GET_FORMAT(): Return a date format string
HOUR(): Extract the hour from a datetime value.
MAKEDATE(): Create a date from the year and day of the year.
MAKETIME(): Create a time value from hours, minutes, and seconds.
MICROSECOND(): Return the microseconds from the time argument.
MINUTE(): Return the minute from the time argument.
MONTH(): Return the month from the date passed.
MONTHNAME(): Return the name of the month.
NOW(): Return the current date and time. A synonym of this function is CURRENT_TIMESTAMP().
PERIOD_ADD(): Add a specific period to a year/month.
PERIOD_DIFF(): Return the number of months between periods.
QUARTER(): Return the quarter from a date argument
SEC_TO_TIME(): Converts seconds to 'hh:mm:ss' format.
SECOND(): Return the second component (0-59) from a given time.
STR_TO_DATE(): Convert a string to a date.
SUBTIME(): Subtract times.
SYSDATE(): Return the time at which the function executes.
TIME(): Extract the time portion of the expression passed.
TIME_FORMAT(): Format a time value.
TIME_TO_SEC(): Return the argument converted to seconds.
TIMEDIFF(): Subtract time values.
TIMESTAMP(): With a single argument, this function returns the date or datetime expression. With two arguments, it returns the sum of the arguments.
TIMESTAMPADD(): Add an interval to a datetime expression.
TIMESTAMPDIFF(): Return the difference of two datetime expressions, using the units specified.
TO_DAYS(): Return the date argument converted to days.
TO_SECONDS(): Return the date or datetime argument converted to seconds since year 0.
UNIX_TIMESTAMP(): Return a Unix timestamp.
UTC_DATE(): Return the current date in UTC.
UTC_TIME(): Return the current time in UTC.
UTC_TIMESTAMP(): Return the current date and time in UTC.
WEEK(): Return the week number of a given date.
WEEKDAY(): Return the weekday index of a given date.
WEEKOFYEAR(): Return the calendar week of the date (1-53).
YEAR(): Return the year of the given date.
YEARWEEK(): Return the year and week components of the specified date.

Find the complete MySQL date function list in the documentation.

PostgreSQL

AGE(): Subtract arguments, producing a “symbolic” result that uses years and months, rather than just days.
CLOCK_TIMESTAMP(): Return the current date and time (changes during statement execution).
CURRENT_DATE(): Return the current date.
CURRENT_TIME(): Return the current time of day.
CURRENT_TIMESTAMP(): Return the current date and time (at the time the current transaction begins).
DATE_ADD(): Add an interval to a timestamp with time zone.
DATE_BIN(): Bin input into specified interval aligned with specified origin.
DATE_PART(): Get the timestamp subfield.
DATE_PART(): Get the specified date section subfield.
DATE_SUBTRACT(): Subtract an interval from a timestamp with time zone.
DATE_TRUNC(): Truncate a date to the specified precision.
EXTRACT(): Get the timestamp subfield.
ISFINITE(): Verify if it is a finite date or +/-infinity date.
JUSTIFY_DAYS(): Adjust the specified date section so that 30-day time periods are represented as months.
JUSTIFY_HOURS(): Adjust the specified date section so that 24-hour time periods are represented as days.
JUSTIFY_INTERVAL(): Adjust interval using JUSTIFY_DAYS() and JUSTIFY_HOURS().
LOCALTIME(): Return the current time of day.
LOCALTIMESTAMP(): Return the current date and time.
LOCALTIMESTAMP(): Current date and time (start of current transaction), with limited precision.
MAKE_DATE(): Create a date from year, month, and day fields (use negative years for BC dates).
MAKE_INTERVAL(): Create a time interval from years, months, weeks, days, hours, minutes, and seconds fields, each of which can default to zero.
MAKE_TIME(): Create a time value from hour, minute, and seconds fields.
MAKE_TIMESTAMP(): Create a timestamp from year, month, day, hour, minute, and seconds fields (use negative years for BC timestamps).
MAKE_TIMESTAMPTZ(): Create timestamp with time zone from year, month, day, hour, minute and seconds fields (negative years signify BC). If TIMEZONE is not specified, the current time zone is used.
NOW(): Return the current date and time.
STATEMENT_TIMESTAMP(): Return the current date and time (start of the current statement).
TIMEOFDAY(): Return the current date and time as a text string.
TRANSACTION_TIMESTAMP(): Return the current date and time (start of current transaction).
TO_TIMESTAMP(): Convert a Unix epoch (seconds since 1970-01-01 00:00:00+00) to a timestamp with time zone.

Discover all PostgreSQL date operators and functions.

SQL Server

SQL Server provides a lot of functions and operators to deal with date, time, and datetime values. Listing them all will take up too much space. Read the official documentation to see them all in action.

DATE SQL Data Types: Best Practices

With all of the date SQL data types available, it’ is useful to keep a couple of things in mind. A key couple of things you need to know are outlined below:

Use DATE for storing only date information without time components.
Use TIMESTAMP or DATETIME when you need to store both date and time information.
Consider using TIMESTAMP with time zone if your application requires accurate time zone conversions and comparisons.
Store dates in SQL date data types and not as strings to make data manipulation easier.
Be mindful of the differences between TIMESTAMP and DATETIME, especially when it comes to time zone handling and range limitations.
Use constraints to enforce data integrity, such as ensuring dates fall within valid ranges.

Conclusion

In this guide, you learned what SQL date data types are and how they work. You now know that each database system has its own data types to handle dates and times. Thanks to the best practices seen here, you have also learned how and when to use these data types.

Dealing with date data types is not always easy, but with DbVisualizer it becomes a breeze! As a full-featured database client, it supports all data types for more than 50 database technologies. This is just one of the many features supported by this powerful tool, which also includes advanced query optimization features and automatic generation of ERD-type schemas. Try DbVisualizer for free today!

FAQ

How to convert from DATETIME to DATE in SQL?

To convert from DATETIME to DATE in SQL, use the DATE() function. Utilize it to wrap the DATETIME column or expression as in the following example:

Copy

        
        
                            
                    1
                    SELECT DATE(datetime_column) AS date_only FROM your_table;

Not all database management systems support this function, so if you receive an error in return, such a function may not be available in your DBMS. Check the documentation pertaining your database management system.

How to convert a string to a DATE in SQL?

To convert a string to a DATE in SQL, use a function like the MySQL STR_TO_DATE() function. Provide the date string as the argument and specify the format as follows:

Copy

        
        
                            
                    1
                    SELECT STR_TO_DATE('2024-06-18', '%Y-%m-%d') AS converted_date;

This function parses the input string according to the specified format and returns a DATE value.

How to get SQL today's date?

To get today's date in SQL, use the CURRENT_DATE() function (if available). For example:

Copy

        
        
                            
                    1
                    SELECT CURRENT_DATE() AS today_date;
                
                            
                    2
                    -- '2024-04-23'

To get also the time, use NOW():

Copy

        
        
                            
                    1
                    SELECT TIME() AS today_date_time;
                
                            
                    2
                    -- '2024-04-23 17:02:59'

Does the SQL BETWEEN operator work with dates?

Yes, the SQL BETWEEN operator works with dates. It can be used to filter data based on a range of dates. See it in action in the example below:

Copy

        
        
                            
                    1
                    SELECT * FROM table_name
                
                            
                    2
                    WHERE date_column BETWEEN '2023-01-01' AND '2023-12-31';

This query retrieves rows where the date_column falls within the specified range.

How to format SQL dates?

To format SQL dates, use:

DATE_FORMAT() in MySQL.
TO_CHAR() in PostgreSQL.
FORMAT() in SQL Server.
TO_CHAR() in Oracle.

These functions allow you to specify the desired format pattern for the date, datetime, time, or timestamp column or expression.

A Guide to the SQL Date Data Types

SQL Date Data Types

MySQL

PostgreSQL

SQL Server

Oracle

DATE vs TIME vs DATETIME vs TIMESTAMP in SQL

DATE

TIME

DATETIME

TIMESTAMP

SQL Date Functions You Need to Know

MySQL

PostgreSQL

SQL Server

DATE SQL Data Types: Best Practices

Conclusion

FAQ

How to convert from DATETIME to DATE in SQL?

How to convert a string to a DATE in SQL?

How to get SQL today's date?

Does the SQL BETWEEN operator work with dates?

How to format SQL dates?

SQL Upsert: Inserting a Record If It Does Not Exist

A Complete Guide to the MySQL COUNT Function

How to Find Elements In an Array: PostgreSQL Guide

MySQL Nullable Columns: Everything You Need to Know

A Complete Guide to the MySQL GROUP_CONCAT Function

How and Why to Add an Index in SQL

MySQL 9.0 and the New EXPLAIN Features

Massive SQL DELETE Operations and Database Purging Explained

MySQL CAST: A Guide to the Data Conversion Function

Postgres Create Array of Numbers: Complete Guide

Product

Partners

Legal

Company

Social