Date sql head image

[Series] Business Ops - 5 SQL Date Functions in No Code to get started​

By Othmane Lamrani · May 10, 2022 · 6 min read

SQL has always been there (since the early 1970’s). 

It’s a language that’s used for database back-ends and systems, but it doesn’t have many user-friendly semantic functions like, say, R.

If you’re not familiar with the language, and you want to learn about SQL — or if you already know SQL and are interested in the cool functions built into it — check out this post on 5 SQL Date Functions in No Code.

These functions help you if your dates are stored as strings, or if you need to change the format of a date field.

Before starting, let’s define the different date formats.

The most common date formats on SQL are: 

  • DATE – format YYYY-MM-DD
  • DATETIME – format: YYYY-MM-DD HH:MI:SS
  • TIMESTAMP – format: YYYY-MM-DD HH:MI:SS

 

We’ll use an example scenario throughout to help demonstrate our point and establish our examples in real data and business environments.

Let’s pretend we’re an analyst for an e-commerce website named OT Shoes that sells shoes.

We may want to manipulate the dates in our data warehouse so that we may perform analyses to assist us monitor growth.

Let’s take this database of OT Shoes orders as an example for this article.

Table use case

1. Cast to date

The Cast to date function converts a string or integer into a date.

If you have your date written as “DD/MM/YYYY”, then you will need to use this function.

In our example, Purchase_date is in the format string (text) when it is a date.

It should be in date format if we want to use this variable in further analysis. Let’s use the function Cast to Date to transform it into a date

SQL query:

				
					Select Order_id,
Quantity,
Purchase_date,
Delivery_date,
CAST(Purchase_date as date) AS Purchase_date2
FROM Purchase

				
			
Cast to date SQL query

No Code:

cast to date nocode

Results

2. Convert date

The Convert date function converts a value (of any type) into a specified datatype.

For example, we need to convert the purchase date into a more readable format.

SQL query:

				
					Select Order_id,
Quantity,
convert(varchar, Purchase_date, 106) AS Purchase_date,
Delivery_date,
FROM Purchase

				
			
Convert data SQL query

No Code:

Results

3. Extract

The Extract function extracts only certain parts of a given timestamp, such as year, month, or day. 

Let’s say that we need a new column with the year of the purchase.

SQL query:

				
					Select Order_id,
Quantity,
Purchase_date,
Delivery_date,
Extract( YEAR FROM CAST(Purchase_date as date) AS Purchase_year
FROM Purchase

				
			
Extract SQL query

No Code:

extract

Results

Extract year restult

4. Add date / Dateadd

The Dateadd function adds a time/date interval to the date and then returns the new date.

According to Company OT Shoe’s policy, all products must be delivered within a maximum of 15 days. Therefore, we want to know the last possible day for delivery: Purchase date + 15 days.

SQL query:

				
					SELECT orddate, agentcode, ROUND(AVG(ordamount)) OVER (
   PARTITION BY agent_code
   ORDER BY ord_date
) running_agent_avg_revenue,
   SUM (ord_amount) OVER (
       PARTITION BY agent_code
       ORDER BY ord_date
   ) running_agent_total_revenue
FROM Purchase

				
			
Add date SQL query

No Code:

Results

DATEADD results

5. Date difference / Datediff

The Datediff function returns the difference between the 2 operand Date in days or months or years.

We want to calculate the difference in days between the delivery date and the purchase date.

SQL query:

				
					Select Order_id,
Quantity,
Purchase_date,
Delivery_date,
DATEDIFF( day, CAST(Delivery_date as date), CAST(Purchase_date as date)) AS Delivery_day
FROM Purchase

				
			
Date difference SQL query

No Code:

DateDiff nocode

Results

Datediff result
Bonus: 2 SQL Advanced functions, still in No Code!

6. Create date

The Create date function creates a new column with a specific day.

We want to create a date that corresponds to the end of the season to make Datediff operations in order to calculate the difference between the date of purchase and the end of the season.

SQL query:

				
					ALTER TABLE Purchase
    ADD End_Date TEXT NULL 
    CONSTRAINT store_code_d DEFAULT "2022-06-01"
    WITH VALUES;

				
			
Create date SQL query

No Code:

Create date nocode

Results

Create date result

7. Timestamp

The Timestamp function returns the date into a timestamp format (current timestamp with chosen timezone).

For instance, we need the exact hour of the purchases for a specific time zone like UTC+2.

SQL query:

				
					Select Order_id,
Quantity,
TIMESTAMP(Purchase_date) AS Purchase_date,
Delivery_date,
FROM Purchase

				
			
Timestamp SQL query

No Code:

Timestamp nocode

Results

Timestamp resultnocode

If you’re interested in starting with a Drag & Drop SQL editor, check out the RestApp website or book a demo.

Share
Share on linkedin
Share on twitter
Share on facebook

Subscribe to our newsletter

Othmane Lamrani
Othmane Lamrani
Othmane is the Growth Manager of RestApp. He's passionate about Entrepreneurship, Data, and Growth. His previous experience in data-position made him have a data-driven vision of acquisition marketing.
Share this article
Share on linkedin
Share on twitter
Share on facebook
Subscribe to our newsletter
Ready to experience data activation
without code?

Product

Activate and combine any data sources without code

Transform your data with our No Code SQL editor

Automate your operational analytics with your business apps

Share your pipelines and collaborate smarter with your teammates

Discover how Data Transformation really means

Find out the new data architecture concept of Data Mesh

Learn how Operational Analytics actives your data

Learn how to deliver great customer experience with real-time data

Solutions

Empower your teammates

Prevent churn

Generate more revenue

Run better campaigns

Drive your metrics

Resources

Stay always up to date on data activation

Get access to tips and tricks to model your data

Discover our always evolving and regularly updated documentation

Find out how we keep your data safe