Archives of the TeradataForum
Message Posted: Fri, 29 Sep 2006 @ 17:23:03 GMT
| Subj: | | Re: Operation on timestamps Teradata vs Oracle |
| |
| From: | | Dieter Noeth |
Andrea Cappelli wrote:
| | Does anyone know if exist a function like the ORACLE Trunc. | |
Should the result be a DATE or a TIMESTAMP?
SELECT
CAST(CURRENT_TIMESTAMP(0) AS DATE),
CAST(CAST(CURRENT_TIMESTAMP(0) AS DATE) AS TIMESTAMP(0));
*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.
Current TimeStamp(0) Current TimeStamp(0)
-------------------- --------------------
2006-09-29 2006-09-29 00:00:00
If it's TRUNC(datecol, format) it's more complicated, e.g.:
SELECT
(EXTRACT(YEAR FROM CURRENT_TIMESTAMP(0)) -1900) * 10000 + 101 (DATE)
(TIMESTAMP(0)) AS "TRUNC(datecol, 'YEAR')";
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
TRUNC(datecol, 'YEAR')
----------------------
2006-01-01 00:00:00
| | I don' t believe that in teradata is it possible with a function, | |
Should be easy to write a UDF for that...
| | and also format the time in this way seems very hard. | |
That format is not following any standard, do you really need that format, without the period it's easier to read:
2006-01-01 10:56:00
| | Another question: Is it possible use the add_month() on a timestamp field? I don' believe. | |
Why don't you try it?
SELECT ADD_MONTHS(CURRENT_TIMESTAMP, 1);
*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.
ADD_MONTHS(Current TimeStamp(6), 1)
-----------------------------------
2006-10-29 17:17:06.710000+02:00
Dieter
| 
