DuckDB is an in-process SQL OLAP database management system designed for analytical workloads. It is known for its speed, efficiency, and ease of use. One of the many functions DuckDB provides is current_date, which is useful for working with date-related data.
In this article, we’ll look at how the current_date function works, along with some straightforward examples.
Julian day is a concept you might occasionally encounter in SQL code or database operations, particularly when working with date and time functions. While it may seem esoteric at first, understanding Julian day can be incredibly useful for handling date calculations, especially in fields like astronomy, data analysis, and historical research.
This article looks at the origins, calculations, and practical applications of Julian day, including examples of converting between Julian day and other date formats in SQL.
Continue readingBelow are the string literal date formats that SQL Server supports for the datetime2 data type. These are the formats that we can provide when setting a datetime2 date from a string literal.
Continue readingPostgreSQL has a datestyle variable that specifies the display format for date and time values, as well as the rules for interpreting ambiguous date input values.Â
We can set the date/time style with the SET datestyle command, the DateStyle parameter in the postgresql.conf configuration file, or the PGDATESTYLE environment variable on the server or client.
Below is an example of using the SET datestyle command to change the datestyle for the current session.
PostgreSQL has a datestyle setting that specifies the display format for date and time values, as well as the rules for interpreting ambiguous date input values.Â
We can check the current value of our datestyle setting by running SHOW datestyle.
PostgreSQL 16 introduced the date_subtract() function that allows us to subtract an interval from a timestamp with time zone.
It computes times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted.
PostgreSQL 16 introduced the date_add() function that allows us to add an interval to a timestamp with time zone.
It computes times of day and daylight-savings adjustments according to the time zone named by the third argument, or the current TimeZone setting if that is omitted.
If you’re getting SQL Server error msg 9837 that tells you “The date value is less than the minimum date value allowed for the data type…”, it sounds like you’re using the DATETRUNC() function with the week date part on a date that would cause the result to backtrack to a date that’s earlier than the date type supports.
This is a rare error that only occurs when using the week date part with the DATETRUNC() function on a very small number of early dates.
One way to deal with this error would be to use iso_week instead of week, if that’s suitable for your particular situation.
SQL Server’s GENERATE_SERIES() function returns a series of numbers within a given interval. But just because it returns numbers, doesn’t mean we can’t leverage its functionality in order to generate a series of time values.
If you need to create a series of time values with evenly spaced intervals between a start and end point, maybe the following technique can help.
Continue readingIf you’re getting SQL Server error msg 9810 that tells you that the datepart “is not supported by date function datetrunc for data type“, it’s probably because you’re using an invalid datepart argument when using the DATETRUNC() function.
For example, this error can occur when using a time date part on a date value (i.e. one that doesn’t have a time component). Conversely, it can also occur when using a date date part on a time value (i.e. one that doesn’t have a date component).
Continue reading