Calculate beginning and ending day of month in Teradata

Teradata has many date and time functions, but sometimes they are not too intuitive. If you need to calculate the first and last date for the current month as part of a batch program, try this SQL:

SELECT 
  MIN(calendar_date) BOM,
  MAX(calendar_date) EOM 
FROM
  sys_calendar.calendar
WHERE (month_of_year , year_of_calendar) IN 
  (SELECT month_of_year, year_of_calendar
   FROM sys_calendar.calendar
   WHERE calendar_date = DATE)

Teradata optimizer anomaly to beware of

I’ve been working with Teradata for more than four years and I just “discovered” this anomaly which was a problem in my SQL scripts that Teradata told me would return 2 quadrillion rows and take 6500 hours to complete.

If you include a column in the SELECT statement that does not come from a database.table/view in the FROM statement, Teradata merrily does a Cartesian join on the database.table/view.

Try this:

SELECT COUNT( <database>.<table or view>.<column>)

Teradata doesn’t seem to care that there is a missing FROM clause and reads every row from <database>.<table or view>.

If the <database>.<table or view>.<column> is part of a larger SQL that has joins to one or more tables and this particular table is not in the FROM clause, Teradata makes a Cartesian join to every other table in the SQL for every row in the "missing" <database>.<table or view>.

My problem was the result of a copy and paste operation from a query that worked, but I didn't catch the change needed for the new query.