Below are seven functions we can use to extract the hours, minutes, seconds, and microseconds from a time value in MySQL.
dates
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.
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.
How to List the Time Zone Names in MySQL
By default, time zone names aren’t included with MySQL. The MySQL installation process creates the appropriate tables, but it doesn’t populate these tables.
That said, it’s very easy to populate these tables.
Once populated, we can list out the time zone names available in our system by querying the mysql.time_zone_name table.
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.
Convert YYYYMMDD to DATE in SQL Server
When working with SQL Server, if we’re given a number that represents a date in the yyyymmdd format, we can use functions like CAST() or CONVERT() to convert that number to a valid date type. This will enable us to perform date operations against it that we might not be able to do when it’s still in numeric form.
MySQL Date Format Specifiers (Full List)
The following table contains a full list of format specifiers that can be used in format strings with functions like DATE_FORMAT(), STR_TO_DATE(), and FROM_UNIXTIME() in MySQL.
How to Set Up Named Time Zones in MySQL
If you want to use named time zones in MySQL, you’ll need to make sure they’ve been configured.
By “named time zones”, I mean being able to use strings like US/Eastern instead of −04:00 or −05:00 when specifying the time zone. For example, when using the CONVERT_TZ() function.
Here’s how to configure named time zones in MySQL.
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.