TERADATA: Date Functions?

• sgmunson

  SSC Guru

  Points: 110644

  More actions

  February 21, 2012 at 11:31 am

  #73288

  Hi all,

  Anyone out there have any experience with TERADATA's version of SQL and the available functions for date handling?

  I have read only access and can't create new objects of any kind, so I just need the syntax of any functions like DATEADD or other similar functioning equivalents. My objective is to have a consistent method that, based on the current date, gets the 1st of this month as a date, and the 1st of the month exactly 1 year prior to the 1st of this month. I need these dates as a filter on a given field, for a table with over 180 Million records.

  Any/all assistance appreciated.

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)

• sgmunson

  SSC Guru

  Points: 110644

  More actions

  February 21, 2012 at 12:55 pm

  #1449929

  Got what I needed... found the ADD_MONTHS function, which worked like a charm.

  I used the rough equivalent of:

  SELECT STUFF, STUFF_2, AND_MORE_STUFF

  FROM TABLENAME

  WHERE DayDt >= ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE) + 1, -12)

  AND DayDt < ADD_MONTHS(DATE - EXTRACT(DAY FROM DATE) + 1, 0)

  Steve (aka sgmunson)
  Rent Servers for Income (picks and shovels strategy)