Snowflake Date & Time Functions Cheat Sheet. Convenient Dates for Analysts

As a Data Analyst / Developer there are often times when you need a quick way to calculate specific dates.

Being inspired by a post I was forwarded, I took the opportunity to convert these functions for usage in the snowflake cloud data platform.

The original post came from DataCeptor

There are multiple ways to get some of these values, and some of these, were simply copy pasted with the increments changed to zero.

As with anything there are trade-offs, feel free to rewrite as you wish for simplicity vs consistency in keystrokes.

These statements are tested with Snowflake SQL as of 2020-09-21.

	— HTTPS:–WWW.DATACEPTOR.COM/SQL-TRICKS
	——————————————————————————————————————————————————————-
	— SNOWFLAKE
	——————————————————————————————————————————————————————-
	SELECT
	DATEADD('MONTH',-1,DATE_TRUNC('MONTH',GETDATE())) AS PREV_MONTH_FIRST_DAY — FIRST DAY OF PREVIOUS MONTH
	,DATEADD('DAY', -1, DATE_TRUNC('MONTH',GETDATE())) AS PREV_MONTH_LAST_DAY — LAST DAY OF PREVIOUS MONTH
	,DATE_TRUNC('MONTH',GETDATE()) AS CURRENT_MONTH_FIRST_DAY — FIRST DAY OF THIS MONTH
	,DATEADD('DAY', -1 , DATEADD(MONTH, +1,DATE_TRUNC('MONTH',GETDATE()))) AS CURRENT_MONTH_LAST_DAY — LAST DAY OF THIS MONTH
	,DATEADD('MONTH',+1,DATE_TRUNC('MONTH',GETDATE())) AS NEXT_MONTH_FIRST_DAY — FIRST DAY OF NEXT MONTH
	,DATEADD('DAY',-1, DATEADD('MONTH',+2,DATE_TRUNC('MONTH',GETDATE()))) AS NEXT_MONTH_LAST_DAY — LAST DAY OF NEXT MONTH
	— QUARTERS
	,DATEADD('QUARTER',-1,DATE_TRUNC('QUARTER', GETDATE())) AS PREVIOUS_QUARTER_FIRST_DAY — FIRST DAY OF PREVIOUS QUARTER
	,DATEADD('DAY', -1, DATE_TRUNC('QUARTER', GETDATE())) AS PREVIOUS_QUARTER_LAST_DAY — LAST DAY OF PREVIOUS QUARTER
	,DATE_TRUNC('QUARTER', GETDATE()) AS CURRENT_QUARTER_FIRST_DAY — FIRST DAY OF THIS QUARTER
	,DATEADD('DAY', -1, DATEADD('QUARTER', 1, DATE_TRUNC('QUARTER', GETDATE()))) AS CURRENT_QUARTER_LAST_DAY — LAST DAY OF THIS QUARTER
	,DATEADD('QUARTER',+1,DATE_TRUNC('QUARTER', GETDATE())) AS NEXT_QUARTER_FIRST_DAY — FIRST DAY OF NEXT QUARTER
	,DATEADD('DAY',-1,DATEADD('QUARTER',+2,DATE_TRUNC('QUARTER', GETDATE()))) AS NEXT_QUARTER_LAST_DAY — LAST DAY OF NEXT QUARTER
	,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())) AS PREVIOUS_YEAR_Q1_FIRST_DAY — FIRST DAY OF 1ST QUARTER OF PREVIOUS YEAR
	,DATEADD('DAY', -1, DATEADD('QUARTER',+1,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())))) AS PREVIOUS_YEAR_Q1_LAST_DAY — LAST DAY OF 1ST QUARTER OF PREVIOUS YEAR
	,DATEADD('QUARTER',+1,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE()))) AS PREVIOUS_YEAR_Q2_FIRST_DAY — FIRST DAY OF 2ND QUARTER OF PREVIOUS YEAR
	,DATEADD('DAY', -1, DATEADD('QUARTER',+2,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())))) AS PREVIOUS_YEAR_Q2_LAST_DAY– LAST DAY OF 2ND QUARTER OF PREVIOUS YEAR
	,DATEADD('QUARTER',+2,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE()))) AS PREVIOUS_YEAR_Q3_FIRST_DAY — FIRST DAY OF 3RD QUARTER OF PREVIOUS YEAR
	,DATEADD('DAY', -1, DATEADD('QUARTER',+3,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())))) AS PREVIOUS_YEAR_Q3_LAST_DAY– LAST DAY OF 3RD QUARTER OF PREVIOUS YEAR
	,DATEADD('QUARTER',+3,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE()))) AS PREVIOUS_YEAR_Q4_FIRST_DAY — FIRST DAY OF 4TH QUARTER OF PREVIOUS YEAR
	,DATEADD('DAY', -1, DATEADD('QUARTER',+4,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())))) AS PREVIOUS_YEAR_Q4_LAST_DAY– LAST DAY OF 4TH QUARTER OF PREVIOUS YEAR
	,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())) AS CURRENT_YEAR_Q1_FIRST_DAY — FIRST DAY OF 1ST QUARTER OF CURRENT YEAR
	,DATEADD('DAY', -1, DATEADD('QUARTER',+1,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())))) AS CURRENT_YEAR_Q1_LAST_DAY — LAST DAY OF 1ST QUARTER OF CURRENT YEAR
	,DATEADD('QUARTER',+1,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE()))) AS CURRENT_YEAR_Q2_FIRST_DAY — FIRST DAY OF 2ND QUARTER OF CURRENT YEAR
	,DATEADD('DAY', -1, DATEADD('QUARTER',+2,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())))) AS CURRENT_YEAR_Q2_LAST_DAY– LAST DAY OF 2ND QUARTER OF CURRENT YEAR
	,DATEADD('QUARTER',+2,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE()))) AS CURRENT_YEAR_Q3_FIRST_DAY — FIRST DAY OF 3RD QUARTER OF CURRENT YEAR
	,DATEADD('DAY', -1, DATEADD('QUARTER',+3,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())))) AS CURRENT_YEAR_Q3_LAST_DAY– LAST DAY OF 3RD QUARTER OF CURRENT YEAR
	,DATEADD('QUARTER',+3,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE()))) AS CURRENT_YEAR_Q4_FIRST_DAY — FIRST DAY OF 4TH QUARTER OF CURRENT YEAR
	,DATEADD('DAY', -1, DATEADD('QUARTER',+4,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())))) AS CURRENT_YEAR_Q4_LAST_DAY– LAST DAY OF 4TH QUARTER OF CURRENT YEAR
	— YEARS
	,DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())) AS PREVIOUS_YEAR_FIRST_DAY — FIRST DAY OF PREVIOUS YEAR
	,DATEADD(DAY , -1, DATEADD(YEAR, 1, DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE())))) AS PREVIOUS_YEAR_LAST_DAY — LAST DAY OF PREVIOUS YEAR
	,DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())) AS CURRENT_YEAR_FIRST_DAY — FIRST DAY OF THIS YEAR
	,DATEADD(DAY , -1, DATEADD(YEAR, 1, DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE())))) AS CURRENT_YEAR_LAST_DAY — LAST DAY OF THIS YEAR
	,DATE_TRUNC('YEAR',DATEADD(YEAR, 1, GETDATE())) AS NEXT_YEAR_FIRST_DAY — FIRST DAY OF NEXT YEAR
	,DATEADD(DAY , -1,DATE_TRUNC('YEAR',DATEADD(YEAR, 2, GETDATE()))) AS NEXT_YEAR_LAST_DAY — LAST DAY OF NEXT YEAR
	— HALF YEARS
	, DATEADD(MONTH, 6, DATE_TRUNC('YEAR',DATEADD(YEAR, -1, GETDATE()))) AS PREVIOUS_YEAR_SECOND_HALF_FIRST_DAY — FIRST DAY OF SECOND HALF OF PREVIOUS YEAR
	, DATEADD(MONTH, 6, DATE_TRUNC('YEAR',DATEADD(YEAR, 0, GETDATE()))) AS CURRENT_YEAR_SECOND_HALF_FIRST_DAY — FIRST DAY OF SECOND HALF OF THIS YEAR
	, DATEADD(MONTH, 6, DATE_TRUNC('YEAR',DATEADD(YEAR, +1, GETDATE()))) AS NEXT_YEAR_SECOND_HALF_FIRST_DAY — FIRST DAY OF SECOND HALF OF NEXT YEAR
	— OTHER
	,GETDATE() AS NOW_DATE_TIME– NOW
	,CAST(DATEADD(D,-1,GETDATE()) AS DATE) AS YESTERDAY– YESTERDAY
	,CAST(DATEADD(D,-0,GETDATE()) AS DATE) AS TODAY — TODAY
	,CAST(DATEADD(D,+1,GETDATE()) AS DATE) AS TOMORROW — TOMORROW
	,DAY(GETDATE()) AS MONTH_DAY_NUMBER — DAY OF MONTH
	,DATEADD(DD, -30, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS DAYS_AGO_30 — 30 DAYS AGO
	,DATEADD(DD, -90, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS DAYS_AGO_90 — 90 DAYS AGO
	,DATEADD(MONTH, -1, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS MONTHS_AGO_01 — 1 MONTHS AGO SINCE LAST MIDNIGHT
	,DATEADD(MONTH, -3, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS MONTHS_AGO_03 — 3 MONTHS AGO SINCE LAST MIDNIGHT
	,DATEADD(MONTH, -6, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS MONTHS_AGO_06– 6 MONTHS AGO SINCE LAST MIDNIGHT
	,DATEADD(MONTH, -12, CAST(DATEADD(D,-0,GETDATE()) AS DATE)) AS MONTHS_AGO_12 — 12 MONTHS AGO SINCE LAST MIDNIGHT

view raw date_calculations.snowflake.sql hosted with ❤ by GitHub

Renaming a Database Table SQL Server & Snowflake

How to rename a database table in snowflake & how to rename a database table in sql server.

Syntax in Snowflake:

use database AdventureWorks2012;
ALTER TABLE public.TBL_REPORT RENAME to public.TBL_KEYWORD_PERFORMANCE_REPORT;

Syntax in SQL Server

USE AdventureWorks2012;   
GO  
EXEC sp_rename 'public.TBL_REPORT', 'TBL_KEYWORD_PERFORMANCE_REPORT'; 

Branching and Merging with Git

When first working with git, the basic steps of using add, commit and push workflow may be sufficient, when you are working alone and in a linear manner.

Working in a team is different, and with changing priorities, you will need to work with different branches (versions) of your code, and be able to switch between them

I found the following video very helpful in explaining each command.

Intro to Git Core Concepts

Git Branch and Merge

Cheat Sheet / Commands:

See this Cheat Sheet from Atlassian Bitbucket for details, the commands are listed here just for quick reference:

git init

git clone <repo>

git add

git commit -m “<message>”

git checkout

git branch

git checkout -b <branch>

git merge <branch>

git branch branchname

git fetch

git push

Define an Alias named “graph” to look at git graph

alias graph=”git log –all –decorate –oneline –graph”

Git Fetch vs Git Pull

fetch vs pull: fetch gets metadata, where as pull gets the metadata and copies those changes to your local system. In the simplest terms, git pull does a git fetch followed by a git merge

Rename a Branch from Stack Overflow

Rename a branch in git from anywhere / regardless of which one is active.

git branch -m <oldname> <newname>

Rename current branch in git.

git branch -m <newname>

Discarding Local Changes / Undoing Changes

Discard All Local Changes Permanently

git reset --hard

Discard all local changes, but save them for possible re-use later

git stash

Solved / Workaround – TypeError: iter_rows() got an unexpected keyword argument ‘row_offset’ –> Change Version of openpyxl

To resolve this error, change version of openpyxl

 

python -m pip install openpyxl==2.5

 

 

ERROR MESSAGE

Traceback (most recent call last):
  File "c:\Users\{username}\.vscode\extensions\ms-python.python-2019.10.44104\pythonFiles\ptvsd_launcher.py", line 43, in <module>
    main(ptvsdArgs)
  File "c:\Users\{username}\.vscode\extensions\ms-python.python-2019.10.44104\pythonFiles\lib\python\old_ptvsd\ptvsd\__main__.py", line 432, in main
    run()
  File "c:\Users\{username}\.vscode\extensions\ms-python.python-2019.10.44104\pythonFiles\lib\python\old_ptvsd\ptvsd\__main__.py", line 316, in run_file
    runpy.run_path(target, run_name='__main__')
  File "C:\opt\Python37-64\lib\runpy.py", line 263, in run_path
    pkg_name=pkg_name, script_name=fname)
  File "C:\opt\Python37-64\lib\runpy.py", line 96, in _run_module_code
    mod_name, mod_spec, pkg_name, script_name)
  File "C:\opt\Python37-64\lib\runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "e:\work\code\01-dev\test.py", line 10, in <module>
    df = petl.todataframe(table=table1)
  File "C:\opt\Python37-64\lib\site-packages\petl\io\pandas.py", line 31, in todataframe
    l = list(table)
  File "C:\opt\Python37-64\lib\site-packages\petl\io\xlsx.py", line 63, in __iter__
    column_offset=self.column_offset)
TypeError: iter_rows() got an unexpected keyword argument 'row_offset'

 

Install Version 2.5 of  openpyxl

PS E:\work\code> python -m pip install openpyxl==2.5
Collecting openpyxl==2.5
  Downloading https://files.pythonhosted.org/packages/62/5d/82b7989dee698891ae3545b157fbbb1b345ec7f78be8167849156c71e71c/openpyxl-2.5.0.tar.gz (169kB)
     |████████████████████████████████| 174kB 6.8MB/s
Requirement already satisfied: jdcal in c:\opt\python37-64\lib\site-packages (from openpyxl==2.5) (1.4)
Requirement already satisfied: et_xmlfile in c:\opt\python37-64\lib\site-packages (from openpyxl==2.5) (1.0.1)
Building wheels for collected packages: openpyxl
  Building wheel for openpyxl (setup.py) ... done
  Created wheel for openpyxl: filename=openpyxl-2.5.0-py2.py3-none-any.whl size=236593 sha256=5886de220495947cb49b34411fac1e326a1e6375558af10c7076e1e4aa597dcf
  Stored in directory: C:\Users\dacostajN\AppData\Local\pip\Cache\wheels\56\e4\1d\cd5608f5018fe46960ed4d30c7137e9bfe9f5f394c01f20fc2
Successfully built openpyxl
Installing collected packages: openpyxl
  Found existing installation: openpyxl 2.6.2
    Uninstalling openpyxl-2.6.2:
      Successfully uninstalled openpyxl-2.6.2
Successfully installed openpyxl-2.5.0

 

ImportError: cannot import name ‘_np_version_under1p14’ from ‘pandas.compat.numpy’ (unknown location)

ImportError: cannot import name ‘_np_version_under1p14’ from ‘pandas.compat.numpy’ (unknown location)

 

Seems like the installation of pandas was incomplete.

To resolve:

 pip uninstall pandas

 pip install --upgrade pandas

 

Managing Data Connections across Users & Environments in Qlik Sense

It seems many users fight with how to effectively manage Data Connections in Qlik Sense between connections and users..

Out of the Box a connection is local to the user that created it.

Therefore when Developer1 creates a connection to myDatabase, we end up with a DataConnection named:

"myDatabase (Developer1)"

When Developer2 tries to write an app there are no connections available. So then they create a new DataConnection and then its named

"myDatabase (Developer2)"

The problem here is that when we develop and deploy these apps, we cannot use a standard connection name across all our apps that are using the same database context.

Out of the Box. We are not able to re-use connections across users. However this is not a bug, this is an excellent security feature.

Creating Shared Connections

1. Create a Connection to the Database / Folder / Resource you need.
   1. e.g. myDatabase (Developer1)
2. Go into the Qlik Sense QMC  –> Start –> Data Connections
3. Click Edit and rename the Connection , if you do not plan to give access to All Data Connections, choose a suitable naming convention.
   • e.g “myDatabase (global)”
4. Create a Security Rule that will grant access to Data Connections based on your needed criteria – I chose to use all members of the ContentAdmin role. (instructions)
5. Data Connections should now be available to the users selected in the SecurityRule.

Using the Same Connection Name across Environments

1. Repeat same process as above. (rename the connections to match what you are using in your lower environments.

 

Dates & Times Exception: replace() takes at most 3 keyword arguments (4 given) – Python

The reason for the replace failing is that the date variable is the incorrect data type.

The parameters for replace are different for the date and datetime data types.

date.replace(year, month, day)

Return a date with the same value, except for those parameters given new values by whichever keyword arguments are specified. For example, if d == date(2002, 12, 31), then d.replace(day=26) == date(2002, 12, 26).

datetime.replace([year[, month[, day[, hour[, minute[, second[, microsecond[, tzinfo]]]]]]]])Return a datetime with the same attributes, except for those attributes given new values by whichever keyword arguments are specified. Note that tzinfo=None can be specified to create a naive datetime from an aware datetime with no conversion of date and time data.

startDate = startDate.replace(hour=0, minute=0, second=0, microsecond=0)

endDate = endDate.replace(hour=0, minute=0, second=0, microsecond=0)

startDate = datetime.combine(startDate, datetime.min.time())

endDate = datetime.combine(endDate, datetime.min.time())

Reference: LINK

https://docs.python.org/2/library/datetime.html

You can validate the data type of your python variable using:

print("dataType of startDate:  %s" % ( type(startDate) ))

Type Before Date Operation: <class ‘datetime.date’>
Type After Date Fix: <class ‘datetime.datetime’>

Qlikview / Qliksense Formulas Formatting Dates & Times

Format DateTime as Time Bucketed into 30 Minute Intervals ( 2 Buckets per Hour)

= Time(Timestamp(round(eventtime, (1/(24*2)))))

Qlikview Display Time in 30 Minute Intervals

Format DateTime as Time Bucketed into 15 Minute Intervals ( 4 Buckets per Hour)

= Time(Timestamp(round(eventtime, (1/(24*4)))))

 

Using Set Analysis To Count Only Page Views Not Page Events (ClickStream Data Processing)

=COUNT({<udo_event_type={"pageview"}>}eventid)

 

Do Easy Year over Year Comparisons By Separating Year from MM/DD (YOY)

=Right('00' & Text(Num(month(calendar_date))),2) & ' / ' & Right('00' & Text(Day(calendar_date)),2)

 

Format Number as Percentage:

= NUM(value, '#,###.#%')

 

Query Data from Tealium DataAccess with C# / ADO.net – ConnectionString Syntax to AWS Redshift Using Npgsql

This is a quick note on connection string syntax.

The Connection String should be placed in your app configuration files and excluded from version control. However: just basic PoC code inline, this is a successful connection string to AWS Redshift from C# .

Setup Instructions – Install Npgsql to Project:

Open Visual Studio

Create New Console App

Go to Tools –> NuGet Package Manager –> Package Manager Console.

From Package Manager Console:

PM> Install-Package Npgsql -Version 4.0.4

Sample Code for Connecting to Redshift and Viewing Records:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Npgsql;

namespace tealiumETL
{
class Program
{
static void Main(string[] args)
{

var connString = "Host=myservername.tealiumiq.com;Port=5439;Username=myUserName;Password=myPassword;Database=myDatabaseName;SSL Mode=Prefer;Trust Server Certificate=true;ServerCompatibilityMode=Redshift;TcpKeepAlive=true";
using (var conn = new NpgsqlConnection(connString))
{
conn.Open();
// Retrieve some rows
using (var cmd = new NpgsqlCommand("select * FROM my_schema_name.events__all_events LIMIT 10 ", conn))
using (var reader = cmd.ExecuteReader())
while (reader.Read())
Console.WriteLine(reader.GetString(0));
}

}
}
}

Run-time error ‘3704’ Operation is not allowed when the object is closed

Run-time error ‘3704’ Operation is not allowed when the object is closed.

 

To resolve the error, ensure the code you execute returns only data as a result, and not the intermediary “rows affected” messages. To achieve this, the following line to your stored procedure / code.

SET NOCOUNT ON;

 

Sample Error Message

Add “SET NOCOUNT ON;” to your stored procedure.

I have identified several blog posts where people experienced the same problem

Run-Time Error 3704 Operation Is Not Allowed The Object Is Closed

Setting NO COUNT ON in VBA ADODB

ADO error “3704” object is closed

Cause

The SQL Server OLEDB provider (SQLOLEDB) has a new behavior designed to provide more accurate information to the caller about what happened in the procedure. Each SQL statement within a stored procedure returns a “result,” either a count of rows affected, or a resultset.

The SQL Server ODBC provider (MSDASQL) does not provide information about the results of individual SQL statements within a stored procedure. The only result that comes back from a stored procedure execution is the result of the SELECT statement if it has one. This is why the problem does not manifest with MSDASQL.

Resolution

To get SQLOLEDB to behave as the SQL ODBC driver did, you must use SET NOCOUNT ON. Put SET NOCOUNT ON in the stored procedure or in the SQL statement used with ADO as shown in the code sample below. Alternatively, you could use MSDASQL.

Status

This behavior is by design.

The problem is described here: