Suppose you have a query that returns multiple date columns, and suppose you want to return the latest date, regardless of which column it came from.
As from SQL Server 2022 we can use the GREATEST() function to easily achieve this outcome.
Tag: dates
How to Convert Time Zones in MySQL using the Time Zone Name
When using a function like CONVERT_TZ() to convert between time zones in MySQL, we provide the date/time value, along with the original time zone and the destination time zone (i.e. the time zone that we’re converting to).
One typical way to do this is to use the time zone offset, such as −05:00 to specify the time zone. Another method is to use the time zone name. However, this requires that we have configured named time zones in MySQL.
Fix Error Msg 237 “There is insufficient result space to convert a money value to int” in SQL Server
If you’re getting error msg 237 that reads There is insufficient result space to convert a money value to int, it’s because you’re trying to convert a money value to an integer but the number’s too big to fit into an integer.
To fix this issue, make sure you convert the value to a data type that can handle the size of the number that you’re trying to convert.
Continue reading7 Functions that Separate Hours, Minutes, Seconds, & Microseconds from a Time Value in MySQL
Below are seven functions we can use to extract the hours, minutes, seconds, and microseconds from a time value in MySQL.
Continue readingThree 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 readingFix 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 readingHow 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.
Continue readingConvert 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 & Time Functions (Full List)
The following is a list of date and time functions available in MySQL. Click on each function name to see an explanation of the function, its syntax, and examples.
Continue reading