Help Center
Go back to application
Help Center
Go back to application
Getting started
IntroductionProduct Flow OverviewUser - Right Management Settings - Client ManagementCheck usage
Data Management
Data ManagementData Import Data Export Data deletion Join Deduplication MappingFilter Product Flow alert Alert Merchant Center Price assets
Optimize flow
Formula EditorText Processing FormulasMathematical FormulasDate Handling FormulasPartition byRegex Variables
Use cases
Product Segmentation Feed AI
Reports and Insights
Reports and InsightsDetailed Import HistoryCAI - Catalog Activation InsightsPSI - Product Segmentation Insight
Updates & Assistance
Software updatesFAQContact us

Date Handling Formulas

SQL Most used formulas

This article introduces you to the most commonly used formulas that can solve a wide variety of problems you may encounter when working with data, particularly in SQL and related environments. Whether you're working with text processing, numerical calculations, or date manipulation, these formulas will help you streamline your work and improve your efficiency. This guide is aimed at users who are familiar with basic SQL functions and are looking to optimize their queries.

Prerequisites

  • Basic knowledge of SQL queries and functions is recommended to fully understand and apply these formulas.

Table of Contents

  1. CURRENT_DATE
  2. CURRENT_DATETIME
  3. CURRENT_TIME
  4. DATE_DIFF
  5. PARSE_DATE
  6. Quick Answers to Common Issues
  7. Related Articles

CURRENT_DATE

Description: This function provides a dynamic way to incorporate the current date into your data operations. It returns the current date, enabling you to perform time-sensitive calculations, comparisons, or data filtering based on the current day.

How to use it: CURRENT_DATE()

Issue: You need to determine if a specific discount or promotion is currently active, filter data based on the current date, or perform calculations that involve the current date, such as calculating the age of a customer or the time since a purchase.

Example:

CASE 
	WHEN CURRENT_DATE() BETWEEN '2022-06-16' AND '2022-06-17' 
	THEN 'Discount Period' 
	ELSE 'Normal Price' 
END

CURRENT_DATETIME

Description: This function provides precise time-based information for your data analysis and operations. It returns the current date and time, enabling you to perform granular time-based filtering, comparisons, or calculations that require both date and time components.

How to use it:CURRENT_DATETIME()

Issue: You need to track events with precise timestamps, analyze data within specific timeframes, or perform calculations that require both date and time information, such as determining the duration of a process or the time remaining for a specific offer.

Example:

CASE 
	WHEN CURRENT_DATETIME() BETWEEN '2022-06-16 10:00' AND '2022-06-17 23:59' 
	THEN 'Discount Applied' 
	ELSE 'Standard Price'
END

CURRENT_TIME

Description: This function provides real-time information about the current time, enabling dynamic time-based actions. It returns the current time, allowing you to implement time-sensitive logic, such as applying different rules based on business hours or triggering events at specific times.

How to use it: CURRENT_TIME()

Issue: You need to display different messages or content to users based on the current time of day, apply different business rules based on working hours, or trigger specific actions or notifications at certain times.

Example:

CASE 
	WHEN CURRENT_TIME() BETWEEN '09:00' AND '18:00' 
	THEN 'Business Hours' 
	ELSE 'After Hours' 
END

DATE_DIFF

Description: This function enables precise time interval calculations for various analytical purposes. It returns the difference in a specified time unit (days, months, etc.) between two dates, allowing you to track durations, analyze trends over time, or compare events based on their time difference.

How to use it: DATE_DIFF(date1, date2, DAY)

Issue: You need to calculate the number of days between two dates, such as the days since a customer's last purchase or the days remaining until a delivery deadline. You can also use it to analyze trends over time or compare events based on their time difference.

Example: DATE_DIFF('2022-06-20', '2022-06-10', DAY)

PARSE_DATE

Description: This function ensures consistency and interoperability when working with dates from different sources. It reformats a date string into a desired format and allows you to perform date operations.

How to use it:PARSE_DATE('%d-%m-%Y', column)

Issue: You are working with data from different sources that use different date formats, which can lead to inconsistencies and errors in analysis or reporting. PARSE_DATE allows you to standardize these formats for consistent and reliable data handling.

Example: PARSE_DATE('%Y-%m-%d', '2023-01-18') > DATE('2022-12-31')
Result: TRUE

Quick Answers to Common Issues

What if a date-related formula is not returning the expected result?

Double-check that the date formats in your data match the expected format of the formula. You might need to use PARSE_DATE to standardize the formats.

Where can I find more information about these date functions and how to use them effectively?

You can access the formula library directly in the editor, where you'll find the majority of these formulas ready to use, along with detailed documentation and examples.

Related Articles

For further assistance with SmartFeeds, consider reviewing these articles:

For additional help, feel free to reach out via our Contact Us page.

Was this article helpful?

Powered by Product Fruits