Below are seven functions we can use to extract the hours, minutes, seconds, and microseconds from a time value in MySQL.
Continue readingTag: date functions
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 readingMySQL 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 readingFix 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 readingPostgreSQL DATE_BIN() Function Explained
In PostgreSQL, the DATE_BIN() function enables us to “bin” a timestamp into a given interval aligned with a specific origin. In other words, we can use this function to map (or force) a timestamp to the nearest specified interval.
This can be handy when we want to truncate a timestamp to a given interval, for example a 10 minute interval. We can specify the interval (e.g. 10 minutes, 15 minutes, 30 minutes, etc), and we can specify the starting point for the interval. Therefore, we can have the interval starting at any odd time we want (it doesn’t need to start on the hour or anything like that).
Continue readingSQLite Introduces the UNIXEPOCH() Function
SQLite 3.38.0 was released on 2nd February 2022. With it came some enhancement to the date and time functions.
One of those enhancements is the addition of the UNIXEPOCH() function. This function returns a unix timestamp. That is, the number of seconds since 1970-01-01 00:00:00 UTC.
4 Functions to Return the Month from a Date in MariaDB
Below are 4 functions that enable you to return the month from a date in MariaDB.
Continue readingAdd a Time Zone Offset to a datetime2 Value in SQL Server (T-SQL)
In SQL Server, the TODATETIMEOFFSET() function was specifically designed to return a datetimeoffset value from a datetime2 value.
Given the fact that the datetime2 data type doesn’t actually support time zone offsets, and datetimeoffset must contain the offset, the TODATETIMEOFFSET() function allows you to specify a time zone offset to use.
This article provides some examples to demonstrate.
Continue readingYEAR() Examples – MySQL
In MySQL, YEAR() is a built-in date and time function that returns the year from a given date expression.
It returns the year as a number in the range 1000 to 9999. For zero dates, it could return 0 or NULL with a warning, depending on the values in your sql_mode.
About the DATE_BUCKET() Function in Azure SQL Edge
T-SQL includes a DATE_BUCKET() function that allows you to arrange data into groups that represent fixed intervals of time. It returns the datetime value that corresponds to the start of each datetime bucket, as defined by the arguments passed to the function.
As far as I’m aware, the DATE_BUCKET() function is only available in Azure SQL Edge at the time of this writing.
Update: DATE_BUCKET() was introduced in SQL Server 2022.