This is the second post in a series on how you can use Tableau Prep to learn SQL. In this post I’ll go over how to create custom dates in Prep & SQL. This may also be handy for people who know SQL and aren’t as familiar with the Tableau date functions. The SQL and Prep examples are all based on the Tableau Superstore data set. The SQL is created for MS SQL Server, if you are using a different flavor of SQL the syntax may be different.
The beauty of Prep is that there is point and click functionality that makes creating date dimensions easy. Need to change a timestamp to a date? Click on the field type icon in the column and change it to a date. Need to change a date to a year number? Click column that has your date field and click on the … Click on the menu that says Convert Dates and you’ll see these options. Note if you want to maintain the date field duplicate the field and do the transformation on the new duplicated field.
If you’re wondering how to create these date dimensions in SQL, the table below will have a snippet of code. The full query is listed below the table.
| Date Type (field type returned) | Tableau | SQL |
| Timestamp to Date (Date) | DATE([Order Date]) or click on the field type and select date | CAST(O.[Order Date] AS DATE) AS ORDER_DATE |
| Day of Month (Number) | DAY([Order Date]) | DAY(O.[Order Date]) AS DAY_NUM |
| Day of Year (Number) | DATEPART(‘dayofyear’,[Order Date]) | DATENAME(dy,O.[Order Date]) AS DAY_OF_YR_NUM |
| Epoch Date (Number) | DATEDIFF(‘second’,#1970-01-01#,[Order Date]) | DATEDIFF(s, ‘1970-01-01 00:00:00’, O.[Order Date]) AS EPOCH_DATE |
| Day of Week Name (Text) | DATENAME(‘weekday’,[Order Date]) | DATENAME(WEEKDAY,O.[Order Date]) |
| Day of Week (Number) | DATEPART(‘weekday’,[Order Date]) | DATEPART(dw,O.[Order Date]) |
| Week Start (Date) | DATE(DATETRUNC(‘week’,[Order Date])) | DATEADD(dd, -(DATEPART(dw, O.[Order Date])-1), O.[Order Date]) AS WEEK_START_DATE |
| Month (Number) | MONTH([Order Date]) or use convert date menu | MONTH(O.[Order Date]) AS MONTH_NUM |
| Month Name (Text) | DATENAME(‘month’,[Order Date]) | DATENAME(MONTH,O.[Order Date]) AS MONTH_NAME |
| Last Day of Month (Date) | DATE(DATEADD(‘day’,-1, DATEADD(‘month’, 1, DATETRUNC(‘month’,[Order Date])) )) | EOMONTH(O.[Order Date]) AS LAST_DAY_OF_MONTH |
| First Day of Month (Date) | DATE(DATETRUNC(‘month’,[Order Date])) | DATEADD(DAY, 1, EOMONTH(O.[Order Date], -1)) AS FIRST_DAY_OF_MONTH |
| Max Month in Data Set (Date) | {FIXED : MAX(DATE(DATETRUNC(‘month’,[Order Date])))} | (SELECT MAX(DATEADD(DAY, 1, EOMONTH(O.[Order Date], -1))) FROM Superstore.dbo.Orders$ O) MAX_MONTH |
| Min Month in Date Set (Date) | {FIXED : MIN(DATE(DATETRUNC(‘month’,[Order Date])))} | (SELECT MIN(DATEADD(DAY, 1, EOMONTH(O.[Order Date], -1))) FROM Superstore.dbo.Orders$ O) MIN_MONTH |
| Today’s Month (Date) | DATE(DATETRUNC(‘month’, TODAY())) | DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) |
| Year (Number) | YEAR([Order Date]) or use convert date menu | YEAR(O.[Order Date]) AS YEAR_NUM |
| Today’s Year (Number) | DATEPART(‘year’,TODAY()) | YEAR(GETDATE()) AS TODAY_YEAR |
| Current Date Time (Timestamp) | NOW() | GETDATE() AS TODAY_DATETIME |
| Quarter Number (Number) | DATEPART(‘quarter’, [Order Date]) | DATEPART(qq,O.[Order Date]) AS QUARTER_NUM |
| Quarter Start (Date) | DATETRUNC(‘quarter’,[Order Date]) | CAST(DATEADD(qq, DATEDIFF(qq, 0, O.[Order Date]), 0) AS DATE) AS QUARTER_START_DATE |
| Quarter End Date (Date) | DATEADD(‘day’,-1,DATEADD(‘quarter’,1,DATETRUNC(‘quarter’,[Order Date]))) | CAST(DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, O.[Order Date]) +1, 0)) AS DATE) AS QUARTER_END_DATE |
This is the query in SQL:
SELECT
O.[Order ID],
CAST(O.[Order Date] AS DATE) AS ORDER_DATE,
DAY(O.[Order Date]) AS DAY_NUM,
DATENAME(dy,O.[Order Date]) AS DAY_OF_YR_NUM,
DATEDIFF(s, ‘1970-01-01 00:00:00’, O.[Order Date]) AS EPOCH_DATE,
DATENAME(WEEKDAY,O.[Order Date]) WEEKDAY_NAME,
DATEPART(dw,O.[Order Date]) AS WEEKDAY_NUM,
DATEADD(dd, -(DATEPART(dw, O.[Order Date])-1), O.[Order Date]) AS WEEK_START_DATE,
MONTH(O.[Order Date]) AS MONTH_NUM,
DATENAME(MONTH,O.[Order Date]) AS MONTH_NAME,
EOMONTH(O.[Order Date]) AS LAST_DAY_OF_MONTH,
DATEADD(DAY, 1, EOMONTH(O.[Order Date], -1)) AS FIRST_DAY_OF_MONTH,
(SELECT MAX(DATEADD(DAY, 1, EOMONTH(O.[Order Date], -1))) FROM Superstore.dbo.Orders$ O) MAX_MONTH,
(SELECT MIN(DATEADD(DAY, 1, EOMONTH(O.[Order Date], -1))) FROM Superstore.dbo.Orders$ O) MIN_MONTH,
DATEADD(DAY, 1, EOMONTH(GETDATE(), -1)) AS TODAY_MONTH,
YEAR(O.[Order Date]) AS YEAR_NUM,
YEAR(GETDATE()) AS TODAY_YEAR,
GETDATE() AS TODAY_DATETIME,
CAST(GETDATE() AS DATE) AS TODAY_DATE,
DATEPART(qq,O.[Order Date]) AS QUARTER_NUM,
CAST(DATEADD(qq, DATEDIFF(qq, 0, O.[Order Date]), 0) AS DATE) AS QUARTER_START_DATE,
CAST(DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, O.[Order Date]) +1, 0)) AS DATE) AS QUARTER_END_DATE,
FROM Superstore.dbo.Orders$ O
Hope that you found this helpful.
Published by