You will love this blog because date functions and formatting differ dramatically between databases, but I have covered that with brilliant examples and clear and concise explanations below.

All of these examples have come from my books and training classes. Please do me a favor and tell your training coordinator that you know the best technical trainer in the world. Ask them to hire me to train at your company, either on-site or with a virtual class. They can see our classes, outlines, and a sample of my teaching at this link on our website.

https://coffingdw.com/education/


Below the video are great date functions on Greenplum. However, you can first witness a two-minute video that joins a Greenplum table with tables from Databricks, Snowflake, Redshift, Google BiqQuery, Synapse, Vertica, Teradata, Oracle, SQL Server, DB2, Yellowbrick, MySQL, Postgres, Microsoft Access, Excel in a single federated query.

The current_date will return today’s date. Add or subtract a number from the CURRENT_DATE, and you are adding or subtracting days from the date. For example, subtracting from two dates, you get the number of days between the two dates.

Above are the keywords you can utilize to get the date or timestamp. These are reserved words that the system will deliver to you when requested.

You use the TO_CHAR function to format dates.

The example below shows how robust the EXTRACT command is at extracting portions of a date, time, or timestamp.

You can extract the decade, day of the week (DOW), and the day of the year (DOY).

The below examples use the date_part function to get the subfields. The text parameter needs to be a string value, not a name, so don’t forget your single quotes.

You can use the to_char extension to extract portions of a date.

The AGE command will tell you the time difference (age) between dates, times, or timestamps.

The below examples use the Date_Trunc function to get the subfields. The text parameter needs to be a string value, not a name, so don’t forget your single quotes. Date_Trunc selects which precision to truncate the input value. The return value is of type timestamp or interval. The valid text names are microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century, and millennium.

The examples below show you how to use day, month, and year intervals.

Posted in Blog