Working with dates in dashdash

Overview

When you’re working with APIs and different data sources, you’ll notice that everyone wants something in just a slightly different way - and it can lead you to some hair pulling. Naturally, it’d be much easier if you could do it programmatically, right? Well, luckily for you, dashdash has got you covered. Today, we’ll talk about our time functions, how to use mathematical operators with dates, and also how to create date ranges so that your API queries are as precise as you need them to be.

Date Functions

Dashdash currently has the following time functions:

Function Description
DAY() Get the day from a date.
MONTH() Get the month from a date.
YEAR() Get the year from a date.
DATE() Create a date using a year, month, and date.
TODAY() Get today’s date in YYYY-MM-DD format.
NOW() Get the current time and date (a timestamp).

So I’m guessing it’s pretty simple why you might need to use NOW() or TODAY(), but in what case would you need to use DAY(), MONTH(), and YEAR()?

Well, they’re great to use when you need to compare one aspect of the date. For example, if you want to see two companies got funding in the same month of a year, it doesn’t make sense to compare the full dates. Instead, you can use MONTH() and YEAR() to extract those to parameters and compare them.

If you want to see a real-world example, you should definitely check out our Slack template for automating birthday messages. We use it to evaluate whether the month and day are the same as the current date, and if it is, we send a birthday message on Slack.

Using operators with dates

You can use certain mathematical operators with dates, like greater than (>) or less that (<), which is very handy when working with APIs. For example, using the SEARCH_COMPANIES_BY_FOUNDER_EXITS_CRUNCHBASE() function you might want to get all companies that were founded after 2015 (>2015) and were sold before and including the year 2017 (<=2017):

=SEARCH_COMPANIES_BY_FOUNDER_EXITS_CRUNCHBASE("New York",">2015",">500000","<=2017")

Here’s a list of the operators you should know when it comes to working with dates:

Operator “Date” definition
> After this date.
>= After and including this date.
< Before this date.
<= Before and including this date.

Date ranges

Lastly, sometimes you might want to include a date range, that is, from one date until another date. For example, from 2015 till 2020. To do this, you need to enclose the dates in square brackets [], and use a semicolon to separate the start and end date. For example: [2015;2020]

And putting it into practice again, let’s have a look at the Crunchbase function from before, but modify it to say that we want companies founded between 2010 and 2015:

=SEARCH_COMPANIES_BY_FOUNDER_EXITS_CRUNCHBASE("New York","[2010;2015]",">500000","<=2017")

:memo: Note: Working with dates in APIs.

When you enter a date into a cell, or when you create a date using one of our data functions, dashdash converts into a date format. Which makes complete sense. However, when you reference that cell as an integration parameter, the date is passed as a numerical value, and not as a string (in other words, it gets passed as a long number, and not as the date you want). So what should you do then? Well, just use TO_TEXT() before referencing that cell, and it’ll be formatted perfectly :slightly_smiling_face: smile:. For example:

TO_TEXT(DATE(2020,01,01))

Summary

And now, equipped with the knowledge of date functions, operators, and ranges, you can build more timely applications :clock:. Now, get out there and build :rocket:!

2 Likes