Excelling at Dates


Pivot! Pivot! Pivot!

If you have ever wanted to analyze some data exported from Marketing Cloud in Microsoft Excel, you might have noticed most dates are displayed incorrectly: some behave like strings and some are dates that appear to be different than you had in your data extension.


Why Can’t Excel Read SFMC dates?

Before we can start to pivot our data, we need to understand why dealing with dates from SFMC is exactly like carrying a couch up a narrow staircase.

This happens, because there’s a mismatch between the format of the exported datetime values and what Excel understands as date values. When we export a data extension, the exported file contains dates in the following format: MM/dd/yyyy hh:mm:ss tt.

Meanwhile Excel recognizes the following formats as dates:

  • dd/MM/yyyy
  • dd/MM/yyyy hh:mm:ss
  • dd/MM/yyyy h:mm:ss tt
  • yyyy-MM-dd
  • yyyy-MM-dd hh:mm:ss
  • yyyy-MM-dd h:mm:ss tt

Excel does not seem to recognize the formatting in which the month is at the first position. Let’s look how Excel would interpret two dates:

StepDay of the month <= 12Day of the month > 12
Date as shown in MCSunday, March 06, 2022 4:01 AMSaturday, August 13, 2022 2:01 PM
Date formatting in the CSV file3/6/2022 4:01:03 AM8/13/2022 2:01:08 PM
Date as shown in Excel3/6/2022 04:01:038/13/2022 2:01:08 PM
Assigned cell formatD1 (d-mmm-yy or dd-mmm-yy)G (General – pretty much a string)
Resulting date03 June 2022None. Excel thinks it’s a string

As you can see in the examples above we have two problematic cases:

  • the month gets interpretted as the day of the month
  • if the day of the month is greater than 12, the whole parsing of the date fails (in our example it was the 13 day of the month, which Excel would read as a number of a non-existing month) and the whole value gets interpretted as a string

On the other hand, 12 days of the year are immune to those isses – those are the days when the month number of the month is the same as the day of the month, for example:

  • November 11th
  • July 7th
NOTE

It does not matter if you use "/" or "-" to separate the different date parts here. Likewise, padding your numbers with leading zeroes is not necesary.

Getting the Right Date Output

We know now that simply exporting a data extension won’t cut it – the dates we want to use in Excel will be broken by the formatting, so we’ll need to work around this issue.

In order to make a Date type field to be recognized as a date we’ll need to store its value in a field of the Text data type (the 50 default length is long enough for this exercise) and put the date in this field in our data extension (you might need to create a new one just for exports).

Simply inserting the our date data into a Text type field is not enough, though. We need to reformat the date in one the following ways:

The Simplest Method

I consider it the easiest, because it does not require you to spell out how the resulting date should look like.

Just Cast() or Convert() the date you want to see in Excel to one of those types depending on how precise you need the output to be:

  • Date – Just the date of an event:Cast(YourDate as Date) as ExcelDate

DateTime2(0) – Full date and time (precise to a second):

Cast(YourDate as DateTime2(0)) as ExcelDateTime2 

Time(0) – just the time data (precise to a second):

Cast(YourDate as Time(0)) as ExcelTime 

If you would want your DateTime2 or Time to deliver fractional parts of seconds, replace the 0 in the parenthesis with the precision you want (it support up to 7 fractional digits of a second and this is the default when you simply omit the parenthesis entirely in either Time or DateTime2).

info

Just be aware that this will cause Excel not recognize the cell values from Time or DateTime2 as dates immediately and requires formatting to be added manually when the exported file is opened.

The Clever Method

Did you know there’s a super powerful Format() function that allows you apply custom string formats in SQL? Well, I for sure didn’t know that and learned that from the Date Functions article from Mateusz Dąbrowski. I highly recommend that article if you want to get into the nitty-gritty of the subject.

This function can utilzie custom format strings that will allow you to export datetimes immediately recognized as dates by Excel:

  • Date without time dataFormat(YourDate, 'dd/MM/yyyy') as Format1,
    Format(YourDate, 'yyyy-MM-dd') as Format2,

Full date with time (24 hour clock)

Format(YourDate, 'yyyy-MM-dd hh:mm:ss') as Format3,Format(YourDate, 'yyyy/MM/dd hh:mm:ss') as Format4,

Full date with time (12 hour clock)

Format(YourDate, 'yyyy-MM-dd h:mm:ss tt') as Format5,Format(YourDate, 'yyyy/MM/dd h:mm:ss tt') as Format6

The Super Complicated Method

If you want to make your SQL to be unreadable, you can build your own date string concatenating various date parts like this:

Concat(    Year(YourDate),     '-',     Right(Concat(0, Month(YourDate)), 2),     '-',     Right(Concat(0, Day(YourDate)), 2),     ' ',     Right(Concat(0, Datepart(hour, YourDate)), 2),     ':',     Right(Concat(0, Datepart(minute, YourDate)), 2)    ':',     Right(Concat(0, Datepart(second, YourDate)), 2)    )

This extreme example includes padding, so any values lower than 10, have an extra 0 added before them, resulting in dates like this: 2022-08-13 14:01:08.

Most of the time I use this method when I’m doing quick, statistical checks in Query Studio. In such cases I would put Concat(Year(EventDate), '-', Month(EventDate)) in the GROUP BY clause.


TL;DR

Insert Cast(YourDate as DateTime2(0)) or Format(YourDate, 'yyyy-MM-dd hh:mm:ss') to a field of the Text data type of the data extension you want to export.