If you’re familiar with PostgreSQL, you might know that you can use the EXTRACT() and the DATE_PART() functions to extract the month from a date. But those functions only allow you to extract the month number.
What if you need the month name?
You can get the month name from a date by using the TO_CHAR() function. This function returns a string based on the timestamp and the template pattern you provide as arguments.
This article provides a list of the standard date and time format strings that can be used when formatting dates and/or times using the FORMAT() function in SQL Server, along with examples using different cultures and data types.
These are the standard date and time format specifiers that are supported by the .NET Framework, and therefore, SQL Server. Each standard date and time format string is an alias for a custom date and time format string.
If you’re getting some really weird results when using the DATEDIFF() function in SQL Server, and you’re convinced the function contains a bug, don’t tear your hair out just yet. It’s probably not a bug.
There are scenarios where the results produced by this function can be pretty whacky. And if you don’t understand how the function actually works, the results will look completely wrong.
Hopefully this article can help clarify how the DATEDIFF() function is designed to work, and provide some example scenarios of where your results might not be as you’d expect.
An interesting thing about the DATEDIFF() function in SQL Server is that it ignores your SET DATEFIRST value.
However, this is not a bug. Microsoft’s documentation for DATEDIFF() clearly states the following:
Specifying
SET DATEFIRSThas no effect onDATEDIFF.DATEDIFFalways uses Sunday as the first day of the week to ensure the function operates in a deterministic way.
In case you don’t know, SET DATEFIRST sets the first day of the week for your session. It’s a number from 1 through 7 (which corresponds to Monday through Sunday).
The initial value for SET DATEFIRST is implicitly set by the language setting (which you can set with the SET LANGUAGE statement). The actual value will depend on the language that is set. For example the default value for the us_english language is 7 (Sunday), whereas the default for the British language is 1 (Monday).
However, you can use a SET DATEFIRST statement to override this so that you can keep using the same language while using a different day for the first day of the week.
But as mentioned, the SET DATEFIRST value has no effect on the DATEDIFF() function. The DATEDIFF() function always assumes that Sunday is the first day of the week regardless of your SET DATEFIRST value.
This can cause some interesting problems when using DATEDIFF() if you don’t know how it works.
If you find yourself in this situation, hopefully the examples on this page can help.
Continue readingThis article contains examples of converting a time value to a datetime2 value in SQL Server.
When you convert a time value to datetime2, extra information is added to the value. This is because the datetime2 data type contains both date and time information. The time data type, on the other hand, only contains time information.
More specifically, the date is set to ‘1900-01-01’ (unless it happens to get rounded up to ‘1900-01-02’), the time component is copied, and according to the Microsoft documentation, the time zone offset is set to 00:00 (even though the datetime2 data type is not time zone aware and doesn’t preserve any time zone offset).
When the fractional seconds precision of the datetime2(n) value is greater than the time(n) value, the value is rounded up.
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). |