Updated September 18, 2023
Introduction to Timestamp to Date in SQL
In SQL, the timestamp is a function that retrieves the current date and time of the SQL server without the database timezone offset. In SQL, CURRENT_TIMESTAMP is used to extract the current date and time. It takes no argument and returns the DateTime value. However, retrieving, storing, and comparing the 2 date and time values in practical situations is necessary and making decisions accordingly. So, it is essential to convert the obtained timestamp in the format according to the requirements. There are various functions provided in the SQL languages which allow the user to handle the above situation.
Table of Content
- Introduction to Timestamp to Date in SQL
- How to Convert Timestamp to Date in SQL with Syntax
- Examples of Timestamp to Date in SQL
How to Convert Timestamp to Date in SQL with Syntax
A timestamp data type also exists in SQL, which has nothing to do with the date and time values. Instead, it exposes the uniquely generated binary numbers in the database whose value keeps on changing on any update/inserts in the database. The data type timestamp has been deprecated now. Instead, the row version is used nowadays to deal with such things.
But, here, the CURRENT_TIMESTAMP function retrieves the current date and time values. And to convert this timestamp in the required date and time values formats. In addition, SQL provides CONVERT and CAST functions that the programmer can use to perform the desired conversion task.
1. CONVERT
In SQL, the CONVERT () function converts any data type’s value into the required data types (as mentioned by the user in the query). To convert the current timestamp to the desired date and time values, the required datatype, expression, and ‘code’ (used to define the required format of date and time to be obtained) are taken as a parameter.
- There are already defined date and time format codes in SQL ranging from 0-141, specifying the date and time values in different formats.
- It is suitable for the coder to learn a few frequently used in the query to perform the task efficiently. However, all the codes are available on the internet easily.
Some of the codes are given below to give you an overview of the formats provided by them:
| Sr. No | Code | Format |
| 1 | 0 | mon dd yyyy hh: mm AM/PM |
| 2 | 9 | mon dd yyyy hh:mm:ss:nnn AM/PM |
| 3 | 13 | dd Mon yyyy hh:mm:ss:nnn AM/PM |
| 4 | 20 | yyyy-mm-dd hh:mm: ss |
| 5 | 21 | yyyy-mm-dd hh:mm:ss:nnn |
| 6 | 22 | mm/dd/yy hh:mm: ss AM/PM |
| 7 | 25 | yyyy-mm-dd hh:mm:ss:nnn |
| 8 | 100 | mon dd yyyy hh: mm AM/PM |
| 9 | 113 | dd Mon yyyy hh:mm:ss:nnn |
| 10 | 126 | yyyy-mm-ddThh:mm:ss:nnn |
| 11 | 2 | yy.mm.dd |
| 12 | 5 | dd-mm-yy |
| 13 | 7 | Mon dd, yy |
| 14 | 8 | hh:mm: ss |
As seen above, to retrieve the current timestamp of the SQL server function which is used is:
CURRENT_TIMESTAMP;
(takes no arguments)Syntax of the CONVERT function of the SQL server to convert the above timestamp:
convert(data_type(length), expr, code) ;Where,
- data_type: It is the data type to convert the input expression to. It can be varchar, char, bigint, smallint, datetime, binary, text, image, etc.
- expr: It is an expression that needs to be converted. In the above query, the expression should be the CURRENT_TIMESTAMP retrieved before.
- code: The date style code is the format in which we want the desired result. Some of the codes are already mentioned in the above table.
2. CAST
The CAST() function performs the same way as CONVERT(), i.e., it converts any data type’s value into the desired data type. Thus, this function can convert the retrieved current timestamp into the date and time values. The CONVERT and CAST function performs the same task, the only difference being that CAST() is a part of ANSI- SQL, whereas CONVERT() is not. But one advantage of the CONVERT() function is that it takes an extra parameter of ‘code’ in which we can style the date and time in the ‘n’ number of formats by passing the style code as a parameter.
Syntax of the CAST function to convert the above timestamp value in the required date and time format:
cast (expr AS data_type(length);Where,
- data_type: It is the data type to convert the expression to. It can be int, bigint, datetime, char, varchar, text, image, etc.
- expr: It is an expression that needs to be cast. It can be an integer, varchar, string, date, etc. But in the above query, the expression should be the CURRENT_TIMESTAMP retrieved before using CURRENT_TIMESTAMP to perform the desired task.
Examples of Timestamp to Date in SQL
Given below are some of the examples showing the conversion of a timestamp to date and time in SQL, along with their outputs:
Some examples of SQL queries showing conversion using the CONVERT function with different ‘code’ parameters:
Example #1
Code:
SELECT convert(varchar, CURRENT_TIMESTAMP,127) ;Output:
Example #2
Code:
SELECT convert(varchar, CURRENT_TIMESTAMP,100) ;Output:
Example #3
Code:
SELECT convert(varchar, CURRENT_TIMESTAMP,9) ;Output:
Example #4
Code:
SELECT convert(varchar, CURRENT_TIMESTAMP,13) ;Output:
Example #5
Code:
SELECT convert(varchar, CURRENT_TIMESTAMP,21) ;Output:
Some examples of SQL queries showing the conversion using the CAST function with different Time and Date data types of the SQL server:
Example #6
Code:
SELECT CAST(CURRENT_TIMESTAMP AS DATE);Output:
Example #7
Code:
SELECT CAST(CURRENT_TIMESTAMP AS datetime2);Output:
Example #8
Code:
SELECT CAST(CURRENT_TIMESTAMP AS DateTime);Output:
Example #9
Code:
SELECT CAST(CURRENT_TIMESTAMP AS DateTimeOffset);Output:
Example #10
Code:
SELECT CAST(CURRENT_TIMESTAMP AS Time);Output:
Example #11
Code:
SELECT CAST(CURRENT_TIMESTAMP AS SmallDateTime);Output:
Conclusion
The above description clearly explains how the timestamp function works in SQL and the practical implementation of converting a timestamp to date. Although for a DBA working on SQL queries, there arise various scenarios when we want the data of date and time in the desired format, we cannot change the table schema every time to satisfy each requirement, so it is essential to understand the conversion clearly to proceed further quickly.
Recommended Articles
We hope that this EDUCBA information on “Timestamp to Date in SQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
