In MariaDB, you can use the DATE_FORMAT() function to return the month name and day name from a date.
This function accepts an optional argument that let’s you specify the language that is used for the result.
Continue readingTag: date format
Add the Ordinal Indicator to a Date in Oracle
In Oracle Database, we can use the TH format element to add the ordinal number suffix to the result of a TO_CHAR() operation when formatting dates.
For example, instead of outputting 10 Feb we could output 10th Feb. Or, instead of outputting 21 century, we can output 21st century. The same applies for spelled date components. For example, instead of Twenty One, we can output Twenty First.
How to Spell Out the Year when Formatting a Date in Oracle
Oracle Database includes a good selection of datetime format elements. that enable us to format dates and times when using the TO_CHAR(datetime) function.
One of these (the YEAR format element) allows us to format a date with the year spelled out. For example, instead of 1972, we’d get NINETEEN SEVENTY-TWO.
We can also use SYEAR so that BC dates are prefixed with a minus sign (-).
3 Ways to Return the Time Zone from a Datetime Value in Oracle
Below are three ways to return time zone information from a datetime value in Oracle Database.
Continue readingAbout the RM Format Element in Oracle
In Oracle Database, the RM format element allows you to return the month in roman numerals when using the TO_CHAR() function.
You can also use the fm modifier to suppress any padding.
How to Get the Century from a Date in Oracle
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.
How to Remove Leading Zeros From Dates in Oracle
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.
RR vs YY in Oracle
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.
How to Remove the Trailing Spaces after the Month Name in Oracle
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.
How to Get the Day, Month, and Year from a Date in SQL
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 reading