EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial Timestamp to Date in SQL
 

Timestamp to Date in SQL

Updated October 4, 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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Table of Content

  • Introduction to Timestamp to Date in SQL
  • Why Convert Timestamps to Dates
  • How to Convert Timestamp to Date in SQL with Syntax
    • CONVERT
    • Cast
    • Date
    • Extract
  • Examples of Timestamp to Date in SQL
  • Best Practices

Why Convert Timestamps to Dates

  • Simplicity and Readability: By removing the time complication, timestamps are converted to dates, simplifying the data and making it easier to read.
  • Aggregation and Grouping: Dates are better suited for aggregating data over time intervals, such as days, months, or years, which helps simplify analysis.
  • Filtering and Querying: Dates make it easier to filter and query SQL for specific date periods.
  • Consistency: For consistent data, converting timestamps to dates ensures consistency and eliminates extraneous temporal details.
  • Compatibility: Because some tools need data in date format, conversion ensures they work with these products.
  • Reducing Complexity: Dates are easier to manipulate, which minimizes the possibility of errors in data manipulation and comparison jobs.

Some common uses of time-series data

  • Stock price movement
  • Sensor tracking (e.g., weather tracking)
  • Rental services (e.g., bike or scooter rentals)

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 changing on any update/insert. 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.

3. DATE

The DATE function extracts the date portion (year, month, and day) from a timestamp or date expression. It is often used when you want to ignore the time portion of a timestamp and work with only the date component. Here’s how you can use it:

SELECT DATE(timestamp_column) AS date_only
FROM your_table;

In this example, timestamp_column contains timestamp values, and DATE(timestamp_column) extracts the date portion from each timestamp. The result will be a new column with only the date.

4. EXTRACT

The EXTRACT function retrieves specific components (e.g., year, month, day, hour, minute) from a timestamp or date. It allows you to access various parts of a timestamp. Here’s how you can use it:

SELECT EXTRACT(YEAR FROM timestamp_column) AS year,
EXTRACT(MONTH FROM timestamp_column) AS month,
EXTRACT(DAY FROM timestamp_column) AS day,
EXTRACT(HOUR FROM timestamp_column) AS hour,
EXTRACT(MINUTE FROM timestamp_column) AS minute
FROM your_table;

In this example, we use EXTRACT to extract the year, month, day, hour, and minute components from the timestamp_column. To suit your needs, you can replace these components with SECOND, QUARTER, WEEK, or DOW (day of the week).

Combining DATE and EXTRACT

You can also combine the DATE and EXTRACT functions to extract specific components from the date portion of a timestamp. For example, to extract the month and day from the date part of a timestamp:

SELECT EXTRACT(MONTH FROM DATE(timestamp_column)) AS month,
EXTRACT(DAY FROM DATE(timestamp_column)) AS day
FROM your_table;

This query extracts the date part from timestamp_column using DATE and then uses EXTRACT to retrieve the month and day components from that date.

Examples of Timestamp to Date in SQL

Below are some examples using different functions:

1. DATE Function

Suppose you want to create a date from a given timestamp, ‘2023-09-28 14:30:00’.

The DATE function extracts the date part from a timestamp or datetime value.

Query:

SELECT DATE('2023-09-28 14:30:00') AS extracted_date;

Output:

DATE Function

2. CAST Function

Suppose you have a string ‘2023-09-2,8’ and want to cast it to a date data type.

The CAST function converts one data type into another.

Query:

SELECT CAST('2023-09-28' AS DATE) AS date_value;

Output:

CAST Function

3. EXTRACT Function

Let’s say you have a timestamp ‘2023-09-28 14:30:0,0’ and want to extract the hour from it.

The EXTRACT function retrieves a specific component (e.g., hour, day, month) from a timestamp or datetime.

Query:

SELECT EXTRACT(HOUR FROM '2023-09-28 14:30:00') AS extracted_hour;

EXTRACT Function

4. CAST Function for Date to String Conversion

Suppose you have a date ‘2023-09-28’ and want to cast it to a string in a specific format.

The CAST function can also convert date or timestamp values to strings with custom formatting.

Query:

SELECT CAST('2023-09-28' AS VARCHAR(10)) AS formatted_date;

Output:

Date to String

Best Practices for Timestamp-to-Date Conversion in SQL

Converting timestamps to dates in SQL involves handling date and time data efficiently. Here are some best practices for timestamp-to-date conversion in SQL:

1. Use Explicit Casting

When converting a timestamp to a date, use explicit casting functions like CAST or CONVERT. This makes your intention clear and helps prevent data type mismatches.

SELECT CAST(timestamp_column AS DATE) AS converted_date
FROM your_table;

2. Be Mindful of Time Zones

Consider the time zone of your data when converting timestamps to dates. SQL databases often store timestamps with time zone information. If necessary, adjust for time zone differences during conversion.

SELECT CAST(timestamp_column AT TIME ZONE 'UTC' AS DATE) AS converted_date
FROM your_table;

3. Handle Null Values

Check for and handle null values in the timestamp column to avoid errors or unexpected results during conversion.

SELECT
CASE
WHEN timestamp_column IS NOT NULL THEN CAST(timestamp_column AS DATE)
ELSE NULL
END AS converted_date
FROM your_table;

4. Use Indexed Columns for Filtering

When filtering data based on converted dates, consider indexing the converted date column for faster query execution.

CREATE INDEX idx_converted_date ON your_table (CAST(timestamp_column AS DATE));

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.

FAQs

Q1. What’s the Difference Between a Timestamp and a DateTime?

Answer: Although they are closely connected, a timestamp and a datetime may differ slightly depending on the situation and the database system. The word “timestamp” is used in many database systems to describe a datetime that also contains information about the time that a particular event happened, making it appropriate for storing events or transactions. The more general term “datetime” describes any date and time, including timestamps.

Q2. How Can I Convert a Timestamp to a Human-Readable Format?

Answer: You can use functions or techniques made available by your database system to transform a timestamp into a human-readable format. CAST, CONVERT, and formatting functions like TO_CHAR (in Oracle) and FORMAT (in SQL Server) are examples of common functions. You can extract and format the date and time components using these functions in the way that suits you best.

Q3. Why Timestamps are Important in Data Management?

Answer: Timestamps are essential to data management for several reasons.

  • Temporal Accuracy: Timestamprecord accurate data, preserving events’ chronological order.
  • Data versioning: You can track data changes across time using timestamps for version control.
  • Timestamps are essential for auditing and logging processes because they show when specific events or actions took place.
  • Data Analysis: Timestamps make it possible for data analysts to conduct time-based analysis, which includes spotting trends, patterns, and anomalies throughout time.

Q4. How Do I Handle Time Zones When Working with Timestamps?

Answer: To ensure accurate representation of time, especially in applications with international users, it is crucial to handle time zones with timestamps. You can standardize specific time zones for your data or utilize timestamps containing time zone information. Consider translating timestamps to the user’s local time zone before presenting them to users.

Q5. How Are Timestamps Stored in Databases?

Answer: Timestamps are frequently recorded in databases as a data type that combines date and time components. The storage format may change depending on the database system. Still, it typically contains year, month, day, hour, minute, and sec data. Some databases also include timestamps with time zone information to maintain accuracy across multiple locations.

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.

  1. SQLite Delete
  2. What is MySQL?
  3. SQLite Show Tables
  4. SQLite foreign key

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW