Skip to main content

Blog Archives

7.4 Date Truncation

Date Truncation is incredibly useful for aggregating, analyzing, and reporting data over specific time intervals, such as years, months, days, or even hours and minutes. By “truncating” a date, you essentially round it down to the nearest specified unit, which can simplify comparisons, aggregations, and trend analysis over periods of time.

DATE_TRUNC( <date_or_time_part>, <date_or_time_expr> )

The DATE_TRUNC function works by taking two arguments: the unit of time to which you want to truncate the date, and the date or timestamp value itself. The function then returns a new date value rounded down to the closest specified time unit, effectively “normalizing” your date data to that level of granularity.

For example, to find the current month for the lead created date you would write SQL like this:

DATE_TRUNC('months', "CreatedDate") “Lead Created Month”

RESULT EXAMPLE (assuming Created Date is ‘2024-02-25’)
2024-02-01

7.3 Date Arithmetic with DateAdd

Adding or subtracting time intervals to dates is straightforward with the DATEADD function. The following is the format for the DATEADD function.

DATEADD( <date_or_time_part>, <value>, <date_or_time_expr> )

The “Date or Time Part” can be any of the date time parts supported by Snowflake:

  • year
  • month
  • day
  • week
  • quarter
  • hour
  • minute
  • second
  • Millisecond

For example, to calculate and add 1-year renewal date from an opportunity’s close date, you can use the following SQL:

DATEADD('years', 1, "CloseDate") AS "1 Year Renewal Date"

7.2 Calculating Date Diffs 

Snowflake simplifies the process of calculating the difference between dates and performing date arithmetic, such as adding or subtracting time intervals from specific dates.

To calculate the difference between two dates, you can utilize the DATEDIFF function. The format of the function is as follows:

DATEDIFF( <date_or_time_part>, <date_or_time_expr1>, <date_or_time_expr2> )

An example and real-world use case would be measuring the difference in days from lead creation to conversion. 

DATEDIFF('days', "CreatedDate", "ConvertedDate") AS "Lead Create to Convert Days"

You can calculate the diff between date and date times using the following date time parts:

  • year
  • month
  • day
  • week
  • quarter
  • hour
  • minute
  • second
  • Millisecond

7.1 Converting from UTC

In the realm of global data management, dealing with time zones is inevitable. Salesforce, for instance, stores all DateTime fields in UTC (Coordinated Universal Time) so it is time zone independent. This is incredibly useful to ensure data that is deployed to users reflects their local timezone when events occur. When you extract data into a data warehouse like Snowflake, you typically do not convert the timezone.

When you run analysis and aggregate data by a DateTime formatted field, you could experience small discrepancies from summary data in Salesforce reports. This is due to the difference between UTC and your local time zone. CONVERT_TIMEZONE function, allows you to adjust DateTime values to the appropriate local time zone, thus ensuring consistency and accuracy in your reports and analyses.

Example:

Suppose your operations are based in California, and you need to convert a UTC CreateDate to the local time in Los Angeles. You can achieve this with the following Snowflake SQL command:

SELECt *, CONVERT_TIMEZONE('UTC', 'America/Los_Angeles', "CreateDate") AS "PST Created Date" FROM SFDC.STAGE.LEAD TZ Create Date"

This function not only caters to specific needs like the above but also supports a broad range of time zones listed in the IANA Time Zone Database, providing flexibility for global operations.

Time Zone Database

Most databases, Snowflake included, provide complete control over timezone data and how you store and query data. In our lesson, we focused solely on setting time zones while querying data. If you want to learn more about storing data in a time-zone-dependent format (https://docs.snowflake.com/en/sql-reference/data-types-datetime), Snowflake provides additional documentation. Unfortunately, this goes beyond the scope of our training.

6.5 Working with NULL Values

Now that we are exploring the world of expressions, we know from experience that data by nature is imperfect. Your Salesforce org contains many fields missing data for a multitude of reasons. Without going deep into the details of how a database works, know that there are scenarios where you have to account for NULL values.

Salesforce approach

This image has an empty alt attribute; its file name is AD_4nXdJeNEx4HdSQHDMVkwfNZPBLddy21njNDj2nYOdSSrQlVnOa_gweCZC5_JyA7UUHU5ssh1G8QxmissaB3hK1-dfWcX2Qjt5-wl0OAYaSt_rD4UTxlNNkNI8O8A8tio42-2gqZHj_G8r8xNenwFmKc8

If you have ever built a formula field, Salesforce will give you the option to treat NULLs as 0s. This is a very useful function in particular when you are working with calculations and values. If your conditional function with CASE or IFF contains values with NULLs, and your output is a value, you will definitely want a condition that handles NULLs.

Snowflake

  • IFNULL(<expr1>, <expr2>): This function checks if expr1 is NULL. If it is, the function returns expr2; otherwise, it returns expr1. This is particularly useful in Salesforce formula fields where you might want to replace NULL values with a default value (like 0) to ensure calculations can proceed without errors.
SELECT

IFNULL("NUMBEROFLOCATIONS__C",0);

ZEROIFNULL(<expr>): This function does the opposite of IFNULL. It returns 0 if the expression (expr) is NULL. This aligns with the Salesforce approach of treating NULL values as 0s, which can simplify calculations by eliminating the need to check for NULL values explicitly. It’s particularly useful in numerical calculations where NULL values can otherwise disrupt summing, averaging, or other aggregate functions.

SELECT

IFNULL("NUMBEROFLOCATIONS__C",0);

6.4 COALESCE

COALESCE returns the first non-NULL value from a list of expressions. It’s like using a formula in Salesforce that checks multiple fields and returns the first field that has a value. This is particularly useful for data that might be spread across multiple fields, ensuring you get a result even if some fields are empty.

Salesforce in particular as a system of record for customer data has numerous scenarios where similarly typed data could exist across multiple fields. Phone, email, and address fields are great examples.


For example, let’s say you want to prepare a list of customers by phone number so you can deliver a call list. COALESCE is a great way to accomplish this task.


Let’s explore a specific SQL case together:

COALESCE(MobilePhone, HomePhone, WorkPhone, ‘No Phone Number Available’)


In this case, COALESCE will return the first non-NULL value. If all fields are NULL, we have ‘No Phone Number’ returned.

Mastering conditional expressions in SQL, such as CASE, IFF, and COALESCE, equips you with a toolkit for dynamic data manipulation and analysis, akin to the capabilities provided by Salesforce formula fields. 

6.3 IFF

IFF is a shorthand version of the CASE statement, available in some SQL dialects (like T-SQL and of course Snowflake). It’s a straightforward way to write a conditional expression, similar to the “IF” function in Excel or Salesforce formulas. It evaluates a condition and returns one value if the condition is true and another if it’s false.

SELECT 

IFF ("COUNTRY" =  "NUMBEROFLOCATIONS__C" >1 AND "COUNTRY" = 'USA' ,'US Muli-Location','Other') "Location Classification"

FROM SFDC.STAGE.LEAD

6.1 – CASE

The CASE statement in SQL is like a series of “IF-THEN” statements in Salesforce formula fields. It lets you set conditions and outcomes within your query. For example, you might use CASE to categorize accounts based on their revenue, much like you might use a formula field to categorize leads in Salesforce.

The good news is if you have created a CASE statement within a Salesforce formula, you can re-use that knowledge in SQL.

WHEN <condition1> THEN <result1>

  [ WHEN <condition2> THEN <result2> ]

  [ ... ]

  [ ELSE <result3> ]

END

LAB:

Let’s work on an existing SQL project from an existing lab and enhance it with a case statement:

In a previous lab, we used the WHERE clause to filter our leads by country and number of locations. What if we wanted to classify and label these locations alongside others instead of filtering our data? A WHERE clause is a simple and efficient way to label data when there is a small number of labels.

SELECT *,

CASE

 WHEN  "NUMBEROFLOCATIONS__C" >1 AND "COUNTRY" = 'USA' THEN 'US Muli-Location'

  WHEN "NUMBEROFLOCATIONS__C" =1 AND "COUNTRY" = 'USA' THEN 'US Single Location'

ELSE 'Other' END "Location Classification"

 FROM SFDC.STAGE.LEAD;