Three ISDATE() Alternatives that Work with DATETIME2 Values in SQL Server

SQL Server’s ISDATE() function checks whether or not an expression is a valid date. However, you may be aware that this function doesn’t work on datetime2 values. On datetime2 values it returns 0, which means it’s not a valid date, even when the value is a valid date.

This is obviously not ideal, because the datetime2 type is a valid date type. Microsoft even recommends that we use datetime2 instead of datetime for our dates, as it aligns with the SQL Standard and it provides more fractional seconds precision.

Anyway, below are three options we can use to check whether a datetime2 value is a valid date.

Continue reading

Fix Msg 529 “Explicit conversion from data type date to int is not allowed” in SQL Server

If you’re getting SQL Server error Msg 529 that reads Explicit conversion from data type date to int is not allowed, it’s because you’re trying to explicitly convert a date data type to an int data type, which is not allowed in SQL Server.

To fix this issue, try converting the date value to a string first, and then to an integer.

Alternatively, change the destination type to one that’s allowed.

Also, check that you’re trying to convert the correct value. For example, you may have selected the wrong column or variable. In this case, selecting the correct column may fix the problem.

Continue reading

Convert DATE to YYYYMMDD in SQL Server

In SQL Server, we can use functions like CONVERT() or FORMAT() to convert a valid date type into a format like yyyymmdd.

This format adheres to the ISO 8601 standard, which defines dates to be written as yyyymmdd, or when using delimiters, as yyyy-mm-dd.

In SQL Server, the date type expresses dates in the yyyy-mm-dd format, but we can use the following technique to remove the delimiters and express the date as yyyymmdd.

Continue reading

Fix Error Msg 8116 “Argument data type datetime2 is invalid for argument 1 of isdate function” in SQL Server

If you’re getting an error that reads Argument data type datetime2 is invalid for argument 1 of isdate function, it’s because you’re passing a datetime2 value to the ISDATE() function, but this function doesn’t work with datetime2 values.

To fix this issue, either pass a valid date type or use the work around below to provide similar functionality that works with datetime2 values.

Continue reading

How TRY_PARSE() Works in SQL Server

In SQL Server, the TRY_PARSE() function returns the result of an expression, translated to the requested data type, or NULL if the conversion fails.

Basically, it works the same as the PARSE() function, except that it returns NULL instead of an error if the cast fails.

Both functions are intended for converting string values to either date/time or number types.

Continue reading