Logo RestApp White
Date sql head image
learning

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

By Othmane Lamrani · May 10, 2022 · 5 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

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

				
			

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

				
			

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

				
			

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

				
			

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

				
			

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;

				
			

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

				
			

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

Additional resources:

https://documentator.io/d/documentation-restapp/

https://restapp.io/model/

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 operational analytics
without code?

Stay up to date to the latest news
on Data Activation

Product

Solutions

Resources