Timestamp to Date in SQL

Introduction to Timestamp to Date in SQL

In SQL, timestamp is a function which is used to retrieve 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, there is a need to retrieve, store, compare the 2 date and time values in practical situations and make decisions accordingly. So, it is important 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.

How to Convert Timestamp to Date in SQL with Syntax

In SQL, a timestamp data type also exists, 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/ insert in the database. 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 which retrieves the current date and time values. And to convert this timestamp in the required formats of date and time values. In addition, SQL provides CONVERT and CAST functions that the programmer can use to perform the desired conversion task.

1. CONVERT

CONVERT() function in SQL is used to convert any value of any data type into the required data types (as mentioned by the user in the query). To convert the current timestamp in the desired date and time values, required datatype, expression, and ‘code’ (used to define the required format of date and time to be obtained) is taken as a parameter.

• In SQL, there are already defined date and time format codes ranging from 0-141 specifying the date and time values in different formats.
• It is good for the coder to learn a few of them used frequently in the query to easily perform the task. 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, in order 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 which needs to be converted. In the above query, the expression should be the CURRENT_TIMESTAMP retrieved before.
• code: It is the date style code, the format in which we want the desired result as. Some of the codes are already mentioned in the above table.

2. CAST

CAST() function performs the same way as CONVERT(), i.e. it too converts the value of any data type in the desired data type. Thus, we can make use of this function to convert the retrieved current timestamp in the date and time values. Both 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 with the CONVERT() function is that it takes an extra parameter of ‘code’ in which we can style the date and time in ‘n’ number of formats simply 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 which needs to be casted. It can be integer, varchar, string, any date, etc. But in the above query, to perform the desired task, the expression should be the CURRENT_TIMESTAMP retrieved before using CURRENT_TIMESTAMP.

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 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 the conversion of a timestamp to Date. Although for a DBA working on the 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 important to understand the conversion clearly in order to proceed further easily.

Recommended Articles

This is a guide to Timestamp to Date in SQL. Here we discuss the introduction, how to convert timestamp to date in SQL with syntax and examples. You may also have a look at the following articles to learn more –

1. SQLite Delete
2. SQLite Drop Table
3. SQLite Show Tables
4. SQLite foreign key