Calculate the Last Day of the Month in T-SQL

Suppose you’d like to select rows in an Salesforce Marketing Cloud data extension created on the last day of the month. Here’s what I use:

The last portion of the where clause finds the first day of the next month and then subtracts one day.

Normally queries like this run in a daily automation.

For further reading:

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







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:


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


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