This article contains examples of converting a time value to a datetimeoffset value in SQL Server using Transact-SQL.
When you convert a time value to datetimeoffset, the date is set to ‘1900-01-01’ and the time is copied. A time zone offset is added and set to +00:00.
This article contains examples of converting a time value to a smalldatetime value in SQL Server.
When you convert a time value to smalldatetime, the date is set to ‘1900-01-01’, and the hour and minute values are rounded up. The seconds and fractional seconds are set to 0.
This article looks at the difference between the MySQL TIMEDIFF() and TIMESTAMPDIFF() functions.
Both functions do a similar thing, but there are some significant differences between the two.
The following table summarizes the difference between these two functions:
TIMEDIFF() |
TIMESTAMPDIFF() |
|---|---|
| Requires 2 arguments. | Requires 3 arguments. |
| Subtracts the 2nd argument from the 1st (date1 − date2). | Subtracts the 2nd argument from the 3rd (date2 − date1). |
| Result is expressed as a time value (and it has the limitations of the time data type). | Result is an integer, expressed by a number of units as provided by the first argument. |
| Accepts time or datetime expressions. | Accepts date or datetime expressions. |
| Both arguments must be the same type (either time or datetime). | Both arguments can be of a different type (date or datetime). |
This article looks at the difference between two MySQL functions; DATEDIFF() and TIMESTAMPDIFF().
Both functions return the difference between two dates and/or times, but the result is different between the two functions.
The following table summarizes the difference between these two functions:
DATEDIFF() |
TIMESTAMPDIFF() |
|---|---|
| Requires 2 arguments. | Requires 3 arguments. |
| Subtracts the 2nd argument from the 1st (expr1 − expr2). | Subtracts the 2nd argument from the 3rd (expr2 − expr1). |
| Result is expressed as a value in days. | Result is expressed as the unit provided by the first argument. |
| Can compare only the date value of its arguments. | Can compare the date and time value of its arguments. |
Two date functions included in MySQL are DATEDIFF() and TIMEDIFF().
Both functions do a similar thing, but with some meaningful differences.
The following table summarizes the difference between these two functions:
DATEDIFF() |
TIMEDIFF() |
|---|---|
| Result is expressed as a value in days. | Result is expressed as a time value. |
| Compares only the date value of its arguments. | Compares the time value of its arguments. |
| Accepts date or date-and-time expressions. | Accepts time or date-and-time expressions. |
| Both arguments can be of a different type (date or date-and-time). | Both arguments must be the same type (either time or date-and-time). |
If you’ve ever needed to find the difference between two dates in SQL Server, you might have used the DATEDIFF() function. This function returns the amount of time between two dates using a datepart specified by you. For example, you could use it to return the number of days between date 1 and date 2. You can also get it to return the number of minutes, seconds, months, years, etc.
The DATEDIFF_BIG() function works exactly the same way, but with one subtle difference: Its return data type.
So the difference between these two functions is the data type of their return value.
DATEDIFF() returns a signed integer (int)DATEDIFF_BIG() returns a signed big integer (bigint)The Year 2038 problem (also referred to as the Y2K38 bug) refers to a problem that some computer systems might encounter when dealing with times past 2038-01-19 03:14:07.
Many computer systems, such as Unix and Unix-based systems, don’t calculate time using the Gregorian calendar. They calculate time as the number of seconds since 1 January 1970. Therefore, in these systems, time is represented as a big number (i.e. the number of seconds passed since 1970-01-01 00:00:00). This is typically referred to as Epoch time, Unix time, Unix Epoch time, or POSIX time. As I write this, Unix time is 1560913841. And as I write this next line, Unix time has incremented to 1560913879.
You might have noticed that SQL Server doesn’t have an equivalent of MySQL‘s UNIX_TIMESTAMP() function.
However, it’s not that difficult to return the Unix timestamp in SQL Server.
The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is simply the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC). Therefore, in SQL Server we can use a couple of T-SQL functions to return this.
When formatting a date using the FORMAT() function in SQL Server, the date will be formatted according to the language of your local session. However, you can override this by specifying a culture to use, or using a custom date format.
This article demonstrates how to explicitly specify a German date format by using the optional “culture” argument of the FORMAT() function. It also demonstrates how to use your own custom date format if that is more desirable.
This article demonstrates how to explicitly format a date in Great Britain English format when using the T-SQL FORMAT() function in SQL Server.
You may or may not need to use this argument, depending on the language of your local session. However, here’s how to explicitly specify Great Britain English date format.