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 undertand 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 h:mm:ss tt
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:
|Step||Day of the month <= 12||Day of the month > 12|
|Date as shown in MC||Sunday, March 06, 2022 4:01 AM||Saturday, August 13, 2022 2:01 PM|
|Date formatting in the CSV file||3/6/2022 4:01:03 AM||8/13/2022 2:01:08 PM|
|Date as shown in Excel||3/6/2022 04:01:03||8/13/2022 2:01:08 PM|
|Assigned cell format||D1 (d-mmm-yy or dd-mmm-yy)||G (General - pretty much a string)|
|Resulting date||03 June 2022||None. 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
It does not matter if you use
- 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 excersise) 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.
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
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
Just be aware that this will cause Excel not recognize the cell values from
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 data
Format(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:
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:
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.
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.