date format

Handling Unix Timestamps in SQL Server

by

Unix timestamps (also known as epoch time) are a simple way of representing a point in time: the number of seconds that have passed since 00:00:00 UTC on January 1, 1970 UTC. They’re popular in APIs, logs, and systems that need a compact, language-neutral way to store time.

If you’re working with SQL Server, you’ll almost certainly run into Unix timestamps eventually. Either you’re getting them from an external system or you need to produce them for one. Let’s walk through how to handle them in SQL Server.

Read more

4 Ways to Format the Current Date as MM/DD/YYYY in SQL Server

by

In SQL Server, we can use functions like GETDATE() to get the current date and time. There are also other functions, like CURRENT_TIMESTAMP, SYSDATETIME(), etc. These functions return values using one of the valid date/time types. For example, GETDATE() and CURRENT_TIMESTAMP return a datetime type, while SYSDATETIME() returns a datetime2(7) value.

Either way, if we want the current date to be displayed using MM/DD/YYYY format, we’ll need to do some extra work.

Fortunately SQL Server provides us with a range of options for doing this, and so we can pick the one that suits our scenario.

With that in mind, here are four ways to format the current date as MM/DD/YYYY in SQL Server.

Read more

Convert MMDDYYYY to DATE in SQL Server

by

Sometimes we get dates in a format that SQL Server has trouble with when we try to convert them to an actual DATE value. One example would be dates in MMDDYYYY format. While it might be easy to assume that SQL Server would be able to handle this easily, when we stop to think about it, this format is fraught with danger.

The MMDDYYYY format is ambiguous. While we might know that the first two digits are for the month, SQL Server doesn’t know this. Some countries/regions use the first two digits for the day (like DDMMYYYY). So if we get a date like, 01032025, how would SQL Server know whether it’s the first day of the third month, or the third day of the first month?

Read more

2 Ways to Get the Month Name from a Date in DuckDB

by

DuckDB offers a pretty good range of functions that enable us to get date parts from date or timestamp value. For example, we can extract the month part from a given date value. In most cases, this will be the month number, for example 08 or just 8.

But sometimes we might want to get the actual month name, like October for example. And other times we might just want the abbreviated month name, like Oct.

Fortunately, DuckDB’s got our back. Here are two ways to return the month name from a date or timestamp value in DuckDB.

Read more

4 Functions to Get the ISO Weekday in DuckDB

by

DuckDB provides us with a good selection of functions for working with dates and timestamps. One of the things we might find ourselves needing to do is extracting the ISO weekday from a date or timestamp—a numeric value where Monday is represented as 1 and Sunday as 7.

This article presents four functions we can use to get the ISO weekday from a date in DuckDB.

Read more

Dealing with Different Date Formats When Using STRPTIME() in DuckDB

by

In DuckDB, the strptime() function converts a date/time string into a valid timestamp value. We pass a format string to the function in order to tell it what format our string uses. This can be handy if we ever need to construct timestamps based on date/time strings that may or may not be in a valid format.

But what if we have multiple date/time strings in different formats?

Fortunately, the strptime() function caters for this scenario too.

Read more