- 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 - GETUTCDATE() Function
The SQL GETUTCDATE() function is used to return the current UTC date and time.
UTC stands for Coordinated Universal Time, which is a standard time reference used worldwide. This function is useful when we want to record events or transactions in a database and ensure that they are accurate and standardized across different time zones in the world. This makes us to analyze the data easily, regardless of where it was recorded.
Syntax
Following is the syntax of the SQL GETUTCDATE() function −
GETUTCDATE()
Parameters
This function does not accept any parameters.
Example
The following example demonstrates the usage of the GETUTCDATE() function −
SQL> SELECT GETUTCDATE() AS GETUTCDATE_RESULT;
Output
When we execute the above query, the output is obtained as follows −
+-------------------------+ | GETUTCDATE_RESULT | +-------------------------+ | 2023-02-17 06:36:37.143 | +-------------------------+
Example
The result format of the SQL GETUTCDATE() function is yyyy-mm-dd hh:mm:ss.mmm. Now, let us customize the result format using the following query −
SQL> SELECT FORMAT(GETUTCDATE(),'dd-mm-yyyy hh:mm:ss tt') AS FORMAT_DATE_AND_TIME
Output
If we execute the above query, the result is produced as follows −
+------------------------+ | FORMAT_DATE_AND_TIME | +------------------------+ | 17-37-2023 06:37:45 AM | +------------------------+
Example
We can use the CONVERT() function to return only the date or time part from the result of the GETUTCDATE() function.
SQL> SELECT CONVERT(DATE, GETUTCDATE()) AS CURRENT_UTC_DATE, CONVERT(TIME, GETUTCDATE()) AS CURRENT_UTC_TIME;
Output
On executing the above query, the output is displayed as follows −
+------------------+------------------+ | CURRENT_UTC_DATE | CURRENT_UTC_TIME | +------------------+------------------+ | 2023-02-17 | 06:41:54.0800000 | +------------------+------------------+
Example
Here, we are trying to add and subtract days from the result of the GETUTCDATE() function.
SQL> SELECT GETUTCDATE() +10 AS DATE_ADD, GETUTCDATE() -15 AS DATE_SUB;
Output
When we execute the above query, the output is obtained as follows −
+-------------------------+-------------------------+ | DATE_ADD | DATE_SUB | +-------------------------+-------------------------+ | 2023-02-27 06:44:32.183 | 2023-02-02 06:44:32.183 | +-------------------------+-------------------------+
Example
We can use the SQL GETDATE() function with the DAY, MONTH, and YEAR functWe can use the SQL GETUTCDATE() function with the DAY, MONTH, and YEAR functions to display the current day, month, and year from the result of GETUTCDATE() function.
SQL> SELECT YEAR(GETUTCDATE()) AS [YEAR], MONTH(GETUTCDATE()) AS [MONTH], DAY(GETUTCDATE()) AS [DAY];
Output
If we execute the above query, the result is produced as follows −
+------+-------+-----+ | YEAR | MONTH | DAY | +------+-------+-----+ | 2023 | 2 | 17 | +------+-------+-----+
Example
Assume we have created a table with the name EMPLOYEE in the SQL database using the CREATE statement as shown in the query below −
SQL> CREATE TABLE EMPLOYEE(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, DATE_OF_BIRTH VARCHAR (20));
Now, let us insert some records in the EMPLOYEE table using INSERT statements as shown in the query below −
SQL> INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(1, 'Dhruv', '2000-12-05'); INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(2, 'Arjun', '2000-03-01'); INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(3, 'Dev', '2001-03-15'); INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(4, 'Riya', '2003-12-05'); INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(5, 'Aarohi', '2000-05-02'); INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(6, 'Lisa', '1999-11-25'); INSERT INTO EMPLOYEE(ID, NAME, DATE_OF_BIRTH) VALUES(7, 'Roy', '2001-05-30');
We can verify whether the table is created or not using the following query −
SQL> SELECT * FROM EMPLOYEE;
The table EMPLOYEE is successfully created in the SQL database.
+-----+--------+---------------+ | ID | NAME | DATE_OF_BIRTH | +-----+--------+---------------+ | 1 | Dhruv | 2000-12-05 | | 2 | Arjun | 2000-03-01 | | 3 | Dev | 2001-03-15 | | 4 | Riya | 2003-12-05 | | 5 | Aarohi | 2000-05-02 | | 6 | Lisa | 1999-11-25 | | 7 | Roy | 2001-05-30 | +-----+--------+---------------+
In the following query, we are calculating the age (according to UTC date and time) of every employee in years −
Note − We can pass the GETUTCDATE() function as an argument.
SQL> SELECT ID, NAME, DATE_OF_BIRTH, DATEDIFF(YEAR, DATE_OF_BIRTH, GETUTCDATE()) AS AGE_IN_YEARS FROM EMPLOYEE;
Output
When we execute the above query, the output is obtained as follows −
+-----+--------+----------------+--------------+ | ID | NAME | DATE_OF_BIRTH | AGE_IN_YEARS | +-----+--------+----------------+--------------+ | 1 | Dhruv | 2000-12-05 | 23 | | 2 | Arjun | 2000-03-01 | 23 | | 3 | Dev | 2001-03-15 | 22 | | 4 | Riya | 2003-12-05 | 20 | | 5 | Aarohi | 2000-05-02 | 23 | | 6 | Lisa | 1999-11-25 | 24 | | 7 | Roy | 2001-05-30 | 22 | +-----+--------+----------------+--------------+
Example
Let us create another table with the name OTT in the SQL database using the CREATE statement as shown in the query below −
SQL> CREATE TABLE OTT(ID INT NOT NULL, SUBSCRIBER_NAME VARCHAR (200) NOT NULL, MEMBERSHIP VARCHAR (200), SUBCRIPTION_DATE DATE NOT NULL);
Now, let us insert some records in the OTT table using INSERT statements as shown in the query below −
SQL> INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(1, 'Dhruv', 'Silver', '2022-12-05'); INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(2, 'Arjun','Platinum', '2021-03-01'); INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(3, 'Dev','Silver', '2021-03-15'); INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(4, 'Riya','Gold', '2022-12-05'); INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(5, 'Aarohi','Platinum', '2020-05-02'); INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(6, 'Lisa','Platinum', '2022-11-25'); INSERT INTO OTT(ID, SUBSCRIBER_NAME, MEMBERSHIP, SUBCRIPTION_DATE) VALUES(7, 'Roy','Gold', '2021-05-30');
We can verify whether the table OTT is created or not using the following query −
SQL> SELECT * FROM OTT
The table OTT is successfully created in the SQL database.
+-----+-----------------+------------+------------------+ | ID | SUBSCRIBER_NAME | MEMBERSHIP | SUBSCRIPTION_DATE| +-----+-----------------+------------+------------------+ | 1 | Dhruv | Silver | 2022-12-05 | | 2 | Arjun | Platinum | 2021-03-01 | | 3 | Dev | Silver | 2021-03-15 | | 4 | Riya | Gold | 2022-12-05 | | 5 | Aarohi | Platinum | 2020-05-02 | | 6 | Lisa | Platinum | 2022-11-25 | | 7 | Roy | Gold | 2021-05-30 | +-----+-----------------+------------+------------------+
In the following query, we are trying to display the remaining number of HOURS (according to UTC date and time) for the subscription plans to expire −
SQL> SELECT SUBSCRIBER_NAME, SUBCRIPTION_DATE, DATEDIFF(HOUR, SUBCRIPTION_DATE, GETUTCDATE()) AS REMAINING_HOURS FROM OTT;
Output
If we execute the above query, the result is produced as follows −
+-----------------+-------------------+-----------------+ | SUBSCRIBER_NAME | SUBSCRIPTION_DATE | REMAINING_HOURS | +-----------------+-------------------+-----------------+ | Dhruv | 2022-12-05 | 1782 | | Arjun | 2021-03-01 | 17238 | | Dev | 2021-03-15 | 16902 | | Riya | 2022-12-05 | 1782 | | Aarohi | 2020-05-02 | 24510 | | Lisa | 2022-11-25 | 2022 | | Roy | 2021-05-30 | 15078 | +-----------------+-------------------+-----------------+
To Continue Learning Please Login