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:
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.
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
Select Order_id, Quantity, Purchase_date, Delivery_date, CAST(Purchase_date as date) AS Purchase_date2 FROM Purchase
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.
Select Order_id, Quantity, convert(varchar, Purchase_date, 106) AS Purchase_date, Delivery_date, FROM Purchase
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.
Select Order_id, Quantity, Purchase_date, Delivery_date, Extract( YEAR FROM CAST(Purchase_date as date) AS Purchase_year FROM Purchase
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.
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
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.
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
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.
ALTER TABLE Purchase ADD End_Date TEXT NULL CONSTRAINT store_code_d DEFAULT "2022-06-01" WITH VALUES;
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.
Select Order_id, Quantity, TIMESTAMP(Purchase_date) AS Purchase_date, Delivery_date, FROM Purchase
Subscribe to our newsletter
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
Empower your teammates
Generate more revenue
Run better campaigns
Drive your metrics
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