The table below contains the valid format string substitutions that you can use with the strftime() function in SQLite.
Tag: date functions
Valid Time String Formats for SQLite Date/Time Functions
Below is a list of valid time string formats that you can use in any date and time function in SQLite.
Continue readingValid Modifiers for SQLite Date/Time Functions
Below is a list of valid modifiers for date and time functions in SQLite.
Continue readingExtract the Month from a Date in PostgreSQL
In PostgreSQL you can use the EXTRACT() function to get the month from a date.
You can also use the DATE_PART() function to do the same thing.
Convert Month Number to Month Name in PostgreSQL
You can use the following code examples in PostgreSQL if you have a month number but you want the month name instead.
Convert Month Name to Month Number in PostgreSQL
In PostgreSQL, if you already have a month name, but you want to convert that name to the month number, you can do this with the EXTRACT() function.
Get the Month Name from a Date in PostgreSQL
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.
Standard Date/Time Format Strings Supported by FORMAT() in SQL Server
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.
DATEDIFF() Returns Wrong Results in SQL Server? Read This.
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.
A Workaround for DATEDIFF() Ignoring SET DATEFIRST in SQL Server (T-SQL Example)
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 reading