Category: SQL Server

How to Convert Between Date Formats in SQL Server using CAST()

Posted on by Ian

In SQL Server, you can use use the CONVERT() function to convert a date value from one date data type to another (or between any other data type for that matter). However, that’s not the only function in the T-SQL toolbox for converting between data types.

The CAST() function is part of the ANSI SQL standard, and it does most of the things CONVERT() does. So in many cases, you have the option of which of these functions you prefer to use.

Many database professionals prefer CAST() due to the fact that it’s part of the ANSI SQL standard, however, others prefer CONVERT() due to the extra functionality that T-SQL’s implementation offers (such as being able to provide a date style).

In any case, this article provides examples of converting between different date formats using CAST().

Continue reading →

How to Convert from One Date Format to Another in SQL Server using CONVERT()

Posted on by Ian

Sometimes when working with databases and other programming environments, you get a date value but it’s in the wrong format/data type. For example, if a date has been generated with an inbuilt date function, it might include both the date and the time, right down to the last nanosecond. And all you want is the day, month, and year, say like this: 2018-01-01.

If this happens while you’re using SQL Server, you can use the CONVERT() function to convert it to another data type. When you do this, the data type will determine the format it appears as.

This article provides examples of using the CONVERT() function in SQL Server to convert a date value to another (date) data type.

Continue reading →

How to Convert a String to a Date/Time in SQL Server using CAST()

Posted on by Ian

In SQL Server, you can use the CAST() function to convert an expression of one data type to another.  This function works almost the same as the CONVERT() function, except that the syntax is slightly different (and CAST() doesn’t accept the style argument).

So if you need to convert a string to a date/time value, you can use the CONVERT() function or the CAST() function.

And if you get an error while using those two functions, the PARSE() function might be just what you need.

This article contains examples using the CAST() function.

Continue reading →

ISDATE() Examples in SQL Server

Posted on by Ian

In SQL Server, you can use the ISDATE() function to check if a value is a valid date.

To be more specific, this function only checks whether the value is a valid date, time, or datetime value, but not a datetime2 value.  If you provide a datetime2 value, ISDATE() will tell you it’s not a date (it will return 0).

This article contains examples of this function.

Continue reading →

How to Find the Last Day of the Month in SQL Server

Posted on by Ian

Starting with SQL Server 2012, the EOMONTH() function allows you to find the last day of any given month. It accepts two arguments; one for the start date, and one optional argument to specify how many months to add to that date.

This article provides examples that demonstrate how EOMONTH() works in SQL Server.

Continue reading →

DATEADD() Examples in SQL Server

Posted on by Ian

In SQL Server, you can use the DATEADD() function to add a specified time period to a given date. You can also use it to subtract a specified time period.

You can also combine DATEADD() with other functions to format the date as required. For example, you could take ‘2020-10-03’, add 10 years, then return the (increased) year component.

This article contains examples to demonstrate.

Continue reading →