Home Page for the TeradataForum
 

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016