- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
SQL - Date & Time
SQL provides multiple datatypes and functions to handle Date and Time values in a database. This is because Date and Time values are represented in various formats. For instance, there are two common ways to represent a date value: DD/MM/YYYY and MM/DD/YYYY. Similarly, there is more than a single way to represent time values.
For a database to recognize such data given in any format, we make use of multiple datatypes and functions.
The only tricky part about storing the Date and Time data in a database is making sure that the values are inserted in the tables with the same format as the datatype.
Different database systems use different datatypes and functions to store and handle the Date and Time data.
Date & Time Datatypes in SQL
Date and time datatypes are used in SQL to store date and time values in various formats. The datatypes available in SQL are listed below.
S.No. | Datatype & Description | Storage |
---|---|---|
1 | datetime It stores date and time both from January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds; with the format: YYYY-MM-DD HH:MI:SS. |
8 bytes |
2 | datetime2 It stores date and time both from January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds. |
6 - 8 bytes |
3 | smalldatetime It stores date and time both from January 1, 1900 to June 6, 2079 with an accuracy of 1 minute. It is stored in the format of YYYY-MM-DD HH:MI:SS. |
4 bytes |
4 | date It stores date only from January 1, 0001 to December 31 9999, in the format: YYYY-MM-DD. |
3 bytes |
5 | time It store time only to an accuracy of 100 nanoseconds. |
3 - 5 bytes |
6 | datetimeoffset It is the same of the datetime2 with the addition of the time zone offset. |
8 - 10 bytes |
7 | timestamp It stores the unique number that gets updated every time a row gets created or modified. It does not correspond to real time and is based on internal time. Each table may have only one timestamp variable. |
Example
In the following example, let us create a table named SALES_DETAILS which accepts only date and time values in different formats.
CREATE TABLE SALES_DETAILS( orderDate DATE, shippingDate DATETIME, deliveredDate TIMESTAMP, time TIME );
To insert values into this table, use the following query −
INSERT INTO SALES_DETAILS VALUES ('2023-02-01', '2023-02-01 :10:00','2023-02-03 :18:00', '18:00');
Output
The table will be created as follows −
orderDate | shippingDate | deliveredDate | time |
---|---|---|---|
2023-02-01 | 2023-02-01 :10:00 | 2023-02-03 :18:00 | 18:00 |
Date & Time Functions in SQL
SQL also provides multiple functions to handle date and time values.
For instance, there are different functions to retrieve the current timestamp in different formats. Let us see some of such functions below −
CURDATE() Function
To get the current date, we use the CURDATE() function in MySQL. The format of the resultant date will be 'YYYY-MM-DD' (string) or YYYYMMMDD (numeric).
SELECT CURDATE();
Output
When we execute the above query, we get the current days date −
CURDATE() |
---|
2023-08-22 |
NOW() Function
The MySQL NOW() function will retrieve the current date and time value as a timestamp based on the context and, the value returned will be in either of the two formats: 'YYYY-MM-DD hh:mm:ss' and 'YYYYMMDDhhmmss'.
SELECT NOW();
Output
When we execute the above SQL query, we get the current date with time as follow −
NOW() |
---|
2023-08-22 15:30:25 |
CURRENT_TIMESTAMP() Function
The MySQL CURRENT_TIMESTAMP() function is used to get the current timestamp. The value returned will be in 'YYYY-MM-DD hh:mm:ss' (string) or YYYYMMDDhhmmss (numeric) format. This function is a synonym for NOW().
SELECT CURRENT_TIMESTAMP();
Output
When we run the above SQL query, we get the following output −
CURRENT_TIMESTAMP() |
---|
2023-08-22 15:31:32 |
To Continue Learning Please Login
Login with Google