The release of SQL Server 2022 came with the introduction of the DATE_BUCKET() function.
The DATE_BUCKET() function allows us to arrange data into groups that represent fixed intervals of time. It returns the date/time value that corresponds to the start of each date/time bucket, as defined by the arguments passed to the function.
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.
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.
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 readingBelow are seven functions we can use to extract the hours, minutes, seconds, and microseconds from a time value in MySQL.
Continue readingSQL 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 readingIf 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 readingBy 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.
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 readingWhen 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.