Tag: date format

How to Use and Store Dates in SQLite

Posted on by Ian

SQLite is a lightweight, self-contained relational database management system that is widely used due to its simplicity and portability. However, one notable aspect of SQLite is its lack of a dedicated DATE or DATETIME storage class. Instead, SQLite stores date and time values as integers, real numbers, or text, depending on how the developer chooses to manage these values.

In this article, we’ll explore how to effectively use and store dates in SQLite, and provide examples that may help you implement date management in your projects.

Continue reading →

4 Ways to Get the ISO Year from a Date in DuckDB

Posted on by Ian

In the ISO-8601 calendar, years begin on the first Monday closest to January 1, meaning the start date can fall between December 29 and January 4. This differs from the Gregorian calendar, where years always start on January 1, leading to potential discrepancies around these dates. Additionally, ISO-8601 years, or just ISO years, can be either 52 or 53 weeks long, depending on their starting point.

This article provides four options for extracting the ISO year from a date in DuckDB, accounting for these unique calendar rules.

Continue reading →

4 Functions that Get the Week From a Date in DuckDB

Posted on by Ian

Working with dates often requires extracting specific components, such as the week number, for analysis or reporting. In DuckDB, there are multiple functions to retrieve the week from a date, each catering to different needs. This article explores four such functions, including how to calculate the ISO week—a standard defined by ISO-8601 where weeks start on Monday and the first week of the year contains the year’s first Thursday.

Continue reading →

How to Format Dates in DuckDB

Posted on by Ian

When working with SQL databases such as DuckDB, dates usually conform to a specific format. In particular, when we create dates in DuckDB, they must conform to the ISO 8601 format (YYYY-MM-DD). It’s the same with time (hh:mm:ss[.zzzzzz][+-TT[:tt]]) and timestamp values (YYYY-MM-DD hh:mm:ss[.zzzzzzzzz][+-TT[:tt]]).

But what if we have a requirement to present these dates or timestamps in a different format?

Fortunately, DuckDB provides us with tools to so. This article explains how to format date and timestamp values according to a specified format.

Continue reading →

List of DuckDB Format Specifiers for Date Formats

Posted on by Ian

In DuckDB, we can use functions like strftime() and strptime() to format date or timestamp values. These functions accept a format string that specifies how the date or timestamp should be formatted.

The format string consists of one or more format specifiers. For example, '%d/%m/%Y' is a format string that consists of three format specifiers. The output from that format specifier might look something like 05/09/2050.

Continue reading →

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 TRY_PARSE() Works in SQL Server

Posted on by Ian

In SQL Server, the TRY_PARSE() function returns the result of an expression, translated to the requested data type, or NULL if the conversion fails.

Basically, it works the same as the PARSE() function, except that it returns NULL instead of an error if the cast fails.

Both functions are intended for converting string values to either date/time or number types.

Continue reading →