Working with dates is one of those things you’ll do constantly in SQL Server, and knowing how to calculate past dates efficiently can save you a ton of time. Whether you’re pulling last month’s sales data or filtering records from the past week, SQL Server gives you several straightforward ways to handle date calculations.
date functions
Understanding the EOMONTH() Function in SQL Server
SQL Server has an EOMONTH() function that returns the last day of the month for a given date. It can be quite handy when you’re working with date calculations in your queries, as it saves you from having to perform calculations just to get the end of the month.
A Quick Look at SQL Server’s DATETRUNC() Function
SQL Server 2022 introduced the DATETRUNC() function, which makes working with date and time values much easier. It trims (or “truncates”) a date/time value down to a specified part (like year, month, or week) while setting all smaller units to their starting value. This helps avoid the common hack of mixing DATEADD() and DATEDIFF() just to snap a timestamp to the beginning of a period.
In this article we’ll look at some examples that demonstrate how it works.
How to Add Days and Months to Dates in SQL Server
When working with SQL Server, you may often find yourself having to shift a date by a certain number of days or months. While it sounds simple, the right function and approach can save you from subtle bugs and errors, especially when dealing with edge cases like leap years or month-end rollovers.
Let’s take a look at how to add days and months to dates in SQL Server.
The Difference Between RANGE() and GENERATE_SERIES() in DuckDB
DuckDB offers two handy functions for generating sequences of numbers: range() and generate_series(). While they both do the same thing, and share basically the same syntax, there is one important difference between them.
The primary difference between them is in their inclusivity behavior with regard to the stop value.
6 Functions for Working with the Unix Epoch in DuckDB
DuckDB offers a versatile set of functions to handle timestamps at various levels of precision. This article explores some of DuckDB’s functions that help us to convert between epoch representations and timestamps.
These specialized time conversion functions can be handy tools when working with temporal data, allowing seamless translation between human-readable timestamps and machine-optimized epoch representations at varying levels of precision.
The Difference Between DATE_DIFF() and DATE_SUB() in DuckDB
In DuckDB, the date_diff() (along with its synonym datediff()) and date_sub() (along with its synonym datesub()) functions allow us to get the difference between two dates. While they might seem similar, they actually calculate date differences in distinct ways that are important to understand for accurate data analysis.
Let’s take a look at the difference between these functions.
Get the Last Day of the Month in DuckDB with LAST_DAY()
DuckDB provides us with a good selection of functions for working with date and timestamp values. One such function is the LAST_DAY() function, which returns the last day of the month of the given date.
The way it works is, we pass a date to the function, and it returns the date of the last day of that month.
An Overview of the TODAY() Function in DuckDB
DuckDB has a today() function that returns the current date. It’s similar to the current_date function, which does the same thing.
Here, we’ll look at how the today() function works, along with some basic examples.
Create a TIMESTAMP Value in DuckDB with MAKE_TIMESTAMP()
The make_timestamp() function in DuckDB is a handy tool for creating timestamp values from individual components. It allows us to construct timestamp values using two alternative approaches; by specifying the number of milliseconds from epoch, or by specifying the year, month, day, hour, minute, and second components separately.
Let’s take a look at the make_timestamp() function, along with some examples.