With Oracle Database, we can use the TO_CHAR(datetime) function to return the century from a datetime value.
To do this, we can use either the CC format element, or the SCC format element. We can combine these with other format elements to do things like, display the ordinal number, spell out the century, etc.
In Oracle Database, you can use the fm (fill mode) format modifier to suppress any leading zeroes that might be applied to a date. This format modifier suppresses padding, which includes leading zeros and trailing blanks.
To use the fm format modifier, include it in your format model when specifying how dates should be formatted. For example when using the TO_CHAR() function to format dates, or in NLS parameters such as the NLS_DATE_FORMAT parameter when setting the date format for your current session.
When formatting dates in Oracle Database, we have the option of using RR and YY to return a two digit year.
These two format elements are similar. The difference is in how they interpret two digits years.
We also have the option of using RRRR and YYYY when returning four digit years.
If you’ve ever used the TO_CHAR() function to format a date that uses the month name, you might have noticed that sometimes the month is returned with right padding.
Most of the major RDBMSs have functions that enable us to extract the day, month, and year from datetime values.
Some RDBMSs provide multiple ways to do this, and others are more limited. Below are examples of extracting the day, month, and year from date values in some of the most popular RDBMSs.
Continue readingBelow are examples that demonstrate how to use SQL to return the short month name from a date in the major RDBMSs.
Continue readingHere’s a list of units that can be used in MySQL datetime and interval functions.
Continue readingOracle Database has the TO_CHAR(datetime) function that enables us to get various date parts from a datetime value.
There’s also the EXTRACT(datetime) function that extracts a specific datetime unit (e.g. day, month, year, etc).
In SQLite, we can use the strftime() function to return datetime values in our chosen format.
Therefore, we can use it to extract the day, month, and year from a date.
Continue readingPostgreSQL provides us with several ways to get the day, month, and year from a date.
Three functions that immediately come to mind are; DATE_PART(), EXTRACT(), and TO_CHAR().