If you have dates expressed as numeric fields (e.g. 20110505) and want to convert them to dates, it is first necessary to cast the integer as a string, and then convert to a date:
select
cast(20110505 as integer) as dt_wid,
cast(dt_wid as char(8)) as dt_wid_char,
cast(dt_wid_char as date format 'YYYYMMDD'),
cast(dt_wid_char as timestamp(6) format 'YYYYMMDD')
Oracle:
Nice and easy in Oracle as we’re given the LAST_DAY function:
select
last_day(sysdate)
from
dual;
Teradata:
Not quite so straightforward as there is no last_day equivalent function, however I found this beautifully elegant piece of code that achieves the same without any nasty case statements:
select
add_months(current_date,1) - extract(day from add_months(current_date,1))
Note: I got an “invalid operation on an ANSI date/time value” when I tried this with current_timestamp so make sure you are only using data datatypes.
The extract function can be used to return a specified component. Valid components are:
YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
Example:
select
extract(month from my_timestamp)
from
my_table
Just a short post – very handy add_months() function in Oracle takes the following paramters:
add_months( date_col, n )
date_col: any column of datatype date
n: a positive or negative integer value representing the number of months to add (or subtract if negative)
e.g.
add_months(created_dt, 2)
This is relatively straightforward, however on doing a little reading around the subject I realised something that perhaps should have always been obvious.
To format a date in Oracle it is necessary to use the TO_CHAR function. What I didn’t appreciate until now is that whenever you return a date from Oracle via SQLPlus or similar it always uses TO_CHAR with a default format of ‘DD/MM/YYYY HH24:MI:SS’
| MM | Numeric month (e.g., 07) |
| MON | Abbreviated month name (e.g., JUL) |
| MONTH | Full month name (e.g., JULY) |
| DD | Day of month (e.g., 24) |
| DY | Abbreviated name of day (e.g., FRI) |
| YYYY | 4-digit year (e.g., 1998) |
| YY | Last 2 digits of the year (e.g., 98) |
| RR | Like YY, but the two digits are “rounded” to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906 |
| AM (or PM) | Meridian indicator |
| HH | Hour of day (1-12) |
| HH24 | Hour of day (0-23) |
| MI | Minute (0-59) |
| SS | Second (0-59) |
Thanks to http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html
To add or subtract a period of time to a data in Teradata you need to use Intervals.
So – to add values to a date:
select
my_date + interval '10' day
from
my_table
And equally to subtract values from a date:
select
my_date - interval '10' day
from
my_table
Valid values for Interval are:
Because it is fun to have more than one way to solve the same problem, you can also use EXTRACT to retrieve specific parts of a timestamp field:
select
Extract (Month
from ISSUE_DT) As “Month”
from
wc_asset_d
select cast ((cast(current_date as format ‘mmm’)) as char(3))
select cast ((cast(current_date as format ‘yyyy’)) as char(4))
Nice and easy that one!
Only caveat is that if you have your dates stored in Timestamp fields you’ll need an extra cast up front to convert the timestamp to a date:
select
cast(cast(cast(issue_dt as date) as format ‘mmm’) as char(3))
from
wc_asset_d
There are two options available to you. Teradata have implemented their own functions alongside the ANSI/ ISO standard functions.
To add, or for that matter subtract, time from an Oracle date you can use the following:
Mike's Tech Blog 