Tag: functions

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 →

DATEDIFF_BIG() Examples in SQL Server

Posted on by Ian

In SQL Server, you can use the DATEDIFF_BIG() function instead of the DATEDIFF() function if you expect the returned value to be really big. For example, if you’re trying to find out how many milliseconds are in a 1000 years, you’ll get an error.

That’s because DATEDIFF() returns an int data type, and the result is too big for that data type to handle. On the other hand, the DATEDIFF_BIG() function returns a signed bigint data type, which means you can use it to return much larger values. In other words, you can use with a much larger range of dates.

Other than that, there’s not really any difference between the two functions.

The article provides examples of using the DATEDIFF_BIG() function in SQL Server.

Continue reading →

6 Functions to Get the Day, Month, and Year from a Date in SQL Server

Posted on by Ian

Transact-SQL includes a bunch of functions that help us work with dates and times. One of the more common tasks when working with dates is to extract the different parts of the date. For example, sometimes we only want the year, or the month. Other times we might want the day of the week. Either way, there are plenty of ways to do this in SQL Server.

In particular, the following functions allow you to return the day, month, and year from a date in SQL Server.

These functions are explained below.

Continue reading →