Date query Help 4

[kiwuji]

I m trying to retreive data from a table and the date field in the table are defined as charter. so i figure if i can extract just the month and year portion of current_date and converted to character, everything should work...but i m getting errors after error.

this is the query:
SELECT orgn_state as ORIGIN
, dest_state as DESTIN
, 'PRIVATE' as "TYPE"
,'CONT' as EQUIP
, '48' as SIZE
, '2003' as "YEAR"
, acct_mm as "MONTH"
, sum(carloads) as VOLUME
from tablename
Where acct_yr = substr(convert_char(year(current_date -1 month)), 2,2)
and acct_mm = convert_char(month(current_date - 1 month))

**Note:
acct_yr and acct_mm are of type CF with X(2) format

 

[Patten]

To extract month or year from a date field use the extract function

syntax: EXTRACT(MONTH FROM datefield)
EXTRACT(YEAR FROM datefield)
EXTRACT(DAY FROM datefield)


SELECT orgn_state as ORIGIN
, dest_state as DESTIN
, 'PRIVATE' as "TYPE"
,'CONT' as EQUIP
, '48' as SIZE
, '2003' as "YEAR"
, acct_mm as "MONTH"
, sum(carloads) as VOLUME
from tablename
Where acct_yr = substring(cast(extract(year from current_date) as char(4)) from 3 for 2)

and acct_mm = (case when characters(trim(extract(month from current_date)))=2
then trim(extract(month from current_date))
else '0'||trim(extract(month from current_date))
end)

 

[dnoeth]

It's always usefull to specifiy error codes/messages.

Please don't use ODBC-SQL functions like MONTH/CONVERT_CHAR, because it's not Teradata SQL. It only works using Queryman with Options -> Query -> "Allow use of ODBC SQL Extensions in queries" enabled.
In Teradata (and ANSI) SQL there's EXTRACT(MONTH FROM date) and CAST(column AS NewDataType).

The syntax for "current_date - 1 month" is wrong, it's "current_date - interval '1' month". But you shouldn't use it anyway, because it's implemented according to ANSI SQL and will return an error message if it will return an illegal date:
e.g. date '2003-03-31' - interval '1' month
Use add_months instead, it's not ANSI, but working ;-)
add_months(current_date, -1)

How is the month stored, leading zeroes or trailing blanks, '1 ' or '01'?
If it's trailing blanks use
trim(extract(month from add_months(current_date, -1)))
else
trim(add_months(current_date, -1) (format 'MM'))

And for the two digit year it's
trim(add_months(current_date, -1) (format 'YY'))

So finally
where
acct_yr = trim(add_months(current_date, -1) (format 'MM'))
and
acct_mm = trim(add_months(current_date, -1) (format 'YY'))

Dieter

 

[kiwuji]

Dear Friends,
Thank you very much for your replies...everything is working great

kiwuji