Stripping Times from T-SQL Dates

When you’re writing queries in Salesforce Marketing Cloud sometimes it’s helpful to truncate the time from date columns — like when you want to compare two date columns, one with a time and one without. You can strip the time off of both to make sure you’re comparing apples to apples.

Here’s an example:

The first column returns the full date and time, the second column cast strips the time portion.

date with timedate without time
2013-04-04 14:37:39.3632013-04-04 00:00:00.000

For further reading:

Geolocation in the Salesforce Marketing Cloud

If you have zip code data for your subscribers and store locations in your Marketing Cloud account, you can use a SQL Query to determine distances between them. With this method you can determine which store is closest to each of your subscribers.

There are a few bits and pieces about geolocation or geo-targeting in the official SFMC documentation, but I think it’s much more helpful to see an example start to finish.

Here are the pieces:

  • A subscriber data extension that includes a zip code column
  • A store data extension that includes a zip code column
  • A zip code data extension that includes X, Y, and Z-axis columns
  • A query that writes to a new data extension

Store_Customers

EmailAddressFirstNameZip
neo@matrix.comNeo94560
morpheus@matrix.comMorpheus00544
trinity@matrix.comTrinity00602

Store_Locations

Store_NumberStore_ZipStore_Phone
55594536555-222-2222
44400612555-333-3333

Zip_Axis

ZipLatitudeLongitudeXAxisYAxisZAxis
0054440.815400000-73.0456000000.220696498-0.7239258680.653624046
0060218.352927000-67.1775320000.368147699-0.8748283040.314869354
0061218.388009000-66.6652790000.375877848-0.8713247720.315450447
9453637.566990000-121.982656000-0.419832255-0.6723248400.609688598
9456037.520602000-122.049566000-0.420878911-0.6722522670.609046658

The Zip_Axis data above has been limited to those zip codes in the example.

I’ve seen people simply store the latitude and longitude for each zip and then calculate the X, Y, and Z values on the fly in the SQL query. However, I prefer calculating those columns beforehand. The queries will be faster and easier to read. You can do that with a query like this:

Store_Customers_Closest_Store

EmailAddressFirstNameZipStore_ZipStore_PhoneRankingDistance
morpheus@matrix.comMorpheus0054400612555333333311596
neo@matrix.comNeo9456094536555222222215
trinity@matrix.comTrinity00602006125553333333134
You can certainly add more store-related columns to this data extension. For simplicity, I only included a few columns.

Geolocation Query

This query pulls it all together. The round function and case statements do the dirty work of the calculations between the two zip codes. The ranking and partition part picks out the closest store per subscriber.

For further reading

Updates

  • 2018-09-16 – Reviewed and updated the sample data, tweaked a table names on the query and re-tested it.