Archives of the TeradataForum

Message Posted: Mon, 03 Mar 2003 @ 20:49:00 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Dynamic date calculations

From:		Dieter Nöth

shivaji wrote:

Current Month

---------------------

sel cast(current_date as DATE) (FORMAT 'MM') or

Only works with BTEQ and it's just for display, the value is still the whole date. You don't have to cast to date, but to char:

sel current_date (FORMAT 'MM') (char(2))

sel extract(month from current_date) or

ANSI compliant SQL

sel month(current_date)

ODBC SQL, works only with Queryman (when "Allow use of ODBC SQL Extansions in queries" option is set)

Previous Month
-----------------------
MONTH(add_months(current_date,-1))

ODBC SQL again, use EXTRACT instead

sel extract(month from add_months(current_date,-1))

Last Day of Last month
------------------------------------
sel cast(cast ( ('01-'||cast(current_date as date FORMAT 'MM-YYYY') )
as
date FORMAT 'DD-MM-YYYY') as date
format 'YYYY-MM-DD') -1

select current_date - extract(day from current_date);

Previous Year
---------------------
sel YEAR(current_date)-1

ODBC SQL again, use EXTRACT instead

Prevous Year month
-------------------------------
sel CAST(add_months(current_date,-1) AS DATE)( FORMAT 'YYYYMM')

Again BTEQ only

--> sel add_months(current_date,-1)( FORMAT 'YYYYMM') (char(6))

ANSI compliant:

sel extract(year from add_months(current_date,-1)) * 100 + extract(month from add_months(current_date,-1));

Current Week Starting
----------------------------------
sel min(calendar_date)
from sys_calendar.calendar
where day_of_week = '7'
and calendar_Date between (current_date - 7) and current_date

Your week starts with Saturday?

If it's sunday:

sel calendar_Date - day_of_week + 1
from sys_calendar.calendar where calendar_Date = current_date;

Previous Week Starting
----------------------------------
sel min(calendar_date)
from sys_calendar.calendar
where day_of_week = '7'
and calendar_Date between (current_date - 14) and (current_date -7)

sel calendar_Date - day_of_week + 1 - 7 from sys_calendar.calendar where calendar_Date = current_date

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>