Tag: dates

Understanding Julian Day

Posted on by Ian

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 reading →

How to Set the datestyle Variable for the Current Session in PostgreSQL

Posted on by Ian

PostgreSQL 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.

Continue reading →

Fix “The date value is less than the minimum date value allowed for the data type” When using SQL Server’s DATETRUNC() Function

Posted on by Ian

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.

Continue reading →

Generate a Time Series in SQL Server

Posted on by Ian

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 reading →

Fix Error Msg 9810 “The datepart … is not supported by date function datetrunc for data type” in SQL Server

Posted on by Ian

If 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 →

Generate Dates Between a Date Range in SQL Server

Posted on by Ian

SQL Server 2022 introduced the GENERATE_SERIES() function, which enables us to create a series of values within a given range. Although this function is limited to just numeric values, we can still combine it with various other functions to create a series of date/time values.

Below are examples of how we can use the GENERATE_SERIES() function to help us get a list of all dates between two given date values.

Continue reading →