Trending October 2023 # Timestamp To Date In Sql # Suggested November 2023 # Top 15 Popular

You are reading the article Timestamp To Date In Sql updated in October 2023 on the website Restaurant12h.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 Timestamp To Date In Sql

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

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

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 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.