The ISO 8601 standard says dates should look like YYYY-MM-DD to avoid confusion between formats like MM/DD/YYYY or DD/MM/YYYY. But sometimes you might need to remove the hyphens and display the date as YYYYMMDD. Maybe your software doesn’t accept special characters, or you’re trying to save space. Whatever the case, here are some simple ways to get today’s date into YYYYMMDD format.
date format
Troubleshooting Date Format Errors in SQL Server Imports
Importing data into SQL Server is usually quite straightforward. That is, until you run into date and time formatting issues. Dates that look fine in a CSV, Excel, or flat file can suddenly throw errors or, worse, silently load with the wrong values. Since SQL Server is strict about how it interprets dates, mismatches between source file formats and SQL Server’s expectations are one of the most common headaches during imports.
This article looks at why these errors happen, what SQL Server expects, and how to troubleshoot these pesky date format issues.
Format Current Date as DD/MM/YYYY in SQL Server
If you’re in a country that uses DD/MM/YYYY format for your dates, then you’ll likely find yourself needing to display the current date in that format when you generate reports or data that’s intended to be read by humans.
Here are four methods to format the current date as DD/MM/YYYY in SQL Server.
Handling Unix Timestamps in SQL Server
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.
4 Ways to Convert MM/DD/YYYY to DATE in SQL Server
Converting a string in ‘MM/DD/YYYY’ format to a DATE data type in SQL Server is a common task. Below are four options for getting the job done.
4 Ways to Format the Current Date as MM/DD/YYYY in SQL Server
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.
When to Use CONVERT() vs CAST() for Date Formatting in SQL Server
When formatting dates in SQL Server you may be wondering whether to use CONVERT() or CAST(). After all, both functions allow us to convert between data types. Let’s take a look at at these two functions and figure out when to use each one.
Convert MMDDYYYY to DATE in SQL Server
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?
Formatting DATE as MMDDYYYY in SQL Server
In SQL Server, we have several options when it comes to formatting a DATE or DATETIME value as MMDDYYYY. The two most common functions for formatting dates like this are CONVERT() and FORMAT().
2 Ways to Get the Month Name from a Date in DuckDB
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.