[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.
Summary
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
No Code:
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:
Results:
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:
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:
Results:
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:
Results:
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:
Results:
If you’re interested in starting with a Drag & Drop SQL editor, check out the RestApp website or book a demo.
Subscribe to our newsletter
Additional resources:
- Top 10 SQL functions to clean your data in No Code. A Complete Guide with Examples
- [Series] How to Build Data Pipelines in No-Code? A 6-Step guide with examples
- What are Window functions and How to use them properly in Low/No-Code? A full guide with Examples
- What is Data Transformation? Definition, process and the new generation of tools
