Converting a string in DD/MM/YYYY format to a DATE type can be done in several ways in SQL Server. This article presents five options along with examples.
dates
Handling International Date Formats When Casting to DATETIME in SQL Server
Working with dates in SQL Server is usually quite straightforward. There’s a good range of date types and functions that we can use to manipulate date/time values.
But international date formats can undo all that simplicity in a heartbeat. Something as simple as casting a string into a DATETIME type can blow up depending on how the server interprets the input. This often happens when you’re dealing with applications or imports that don’t stick to a single culture or regional setting.
Let’s walk through an example and see why SQL Server behaves this way, and more importantly, how to handle it correctly.
Get First and Last Day of Month in SQL Server for Reports
When building reports in SQL Server, it’s common to filter or group data by month. A frequent requirement is to get the first and last day of a given month, often so you can build ranges for your WHERE clauses or generate summary tables. SQL Server gives us several ways to do this, and once you know the patterns, it’s quick to adapt for different reporting needs.
Format Current Date as YYYYMMDD in SQL Server
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.
A Quick Look at SQL Server’s DATETRUNC() Function
SQL Server 2022 introduced the DATETRUNC() function, which makes working with date and time values much easier. It trims (or “truncates”) a date/time value down to a specified part (like year, month, or week) while setting all smaller units to their starting value. This helps avoid the common hack of mixing DATEADD() and DATEDIFF() just to snap a timestamp to the beginning of a period.
In this article we’ll look at some examples that demonstrate how it works.
How to Add Days and Months to Dates in SQL Server
When working with SQL Server, you may often find yourself having to shift a date by a certain number of days or months. While it sounds simple, the right function and approach can save you from subtle bugs and errors, especially when dealing with edge cases like leap years or month-end rollovers.
Let’s take a look at how to add days and months to dates in SQL Server.
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.