Data conversion errors can be a frequent source of frustration when working with databases. And SQL Server is no exception. Such errors can interrupt workflows and lead to inconsistent results. While data conversion errors often happen during explicit conversions, they aren’t unique to this. Oftentimes the error can be due to an implicit conversion.
This article outlines five of the most common data conversion errors and provides practical steps to avoid them.
If you’ve worked with Oracle or MySQL before, you may have used the INSTR() function to find the position of a substring inside a string. But when you’re porting code to SQL Server, you’ll quickly notice that INSTR() isn’t available. Instead, you’ll need to use either CHARINDEX() or PATINDEX(), depending on what you’re trying to do.
The good news is that both functions are pretty straightforward once you know the difference. CHARINDEX() handles simple substring searches, while PATINDEX() adds the ability to use patterns. In this article, we’ll walk through how each one works and how you can swap them in when you’d otherwise use INSTR().
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.
In relational database management systems (RDBMSs) like SQL Server, the primary key constraint is a fundamental element that uniquely identifies each record in a table and enforces data integrity. However, there are situations where you might need to drop and then recreate this constraint. Such situations could include modifying the primary key columns, changing data types, resolving design issues, etc.
Understanding how to safely remove and reapply primary key constraints is extremely important for maintaining database consistency and minimizing downtime during schema changes.
In this article, we’ll walk through how to safely drop and then re-add a primary key constraint without breaking your database.
In SQL Server, when working with temporary tables, it’s common to ensure a temp table with the same name doesn’t already exist before creating it. This prevents any unwanted errors. SQL Server doesn’t support CREATE TABLE IF NOT EXISTS like some other DBMSs do, so you must explicitly check and drop the table beforehand.
Converting between time zones in SQL Server has evolved throughout the years. Prior to SQL Server 2016, there was no simple, built-in function to handle this task. We had to use a complex, multi-step approach involving functions like SWITCHOFFSET() and TODATETIMEOFFSET(), and we had to manually account for Daylight Saving Time (DST) rules for each time zone. This method was often prone to error and required constant maintenance to keep up with changing time zone and DST regulations.
Sometimes you might get dates in a non-standard format, such as DDMMYYYY. By “non-standard” I mean a format that SQL Server doesn’t recognize as a date. Such a format is actually a little ambiguous. Some dates presented in this format could be mistaken for another date. For example, 02082026 could be August 2, 2026, or it could be February 8, 2026 depending on which locale you’re using.
Therefore, we need to do a bit of work in order to get SQL Server to recognize it as a date. Once that’s done, it’s a simple matter of converting it to an actual DATE type.
Below are a few options for converting a DDMMYYYY string to a DATE in SQL Server.
In SQL Server, STRING_AGG() is an aggregate function that concatenates string values from a group into a single string. It’s a handy tool for doing things like creating comma-separated lists from related data.
In this article we’ll check out how to use STRING_AGG() with different separators. We’ll also see how we can control the order of the concatenated strings.
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 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.