date subtraction 1

[sophie123]

I would like to find out what is the syntax for subtrating a current_date from in_service date date is mm/dd/yyyy
so something like
date - in service date = new tenure date
My PM want to see a list of home long individuals have had service with our company.
Thanks

 

[tdatgod]

you can simply subtract 2 date fields in Teradata.


select ( current_date - in_service_date ) ;


subtracting 2 dates in ANSI syntax returns an Interval.

By default in Teradata the interval is Day if not specified. If you want days don't specify the Interval as specifying Day(4) would limit the answer to 9999 days.


sel current_date - date '2004-01-01';

*** Query completed. One row found. One column returned.
*** Total elapsed time was 2 seconds.

(Date-2004-01-01)
-----------------
399

If you want something else you can specify which interval you want.

year(4)
year(4) to Month
Month(4)
Day(4)

sel current_date - date '1970-04-01' month(4);

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

(Date - 1970-04-01) MONTH
-------------------------
418


The (4) means you want at Most 4 DIGITS ( 9999 )in your answer. That is currently the maximum allowed for Teradata. You can specify

(1) which means the answer is limited to 0-9
(2) which means the answer is limited to 0-99
(3) which means the answer is limited to 0-999
(4) which means the answer is limited to 0-9999

If you exceed the value requested you will get the following error.

*** Failure 7453 Interval field overflow.


There are other intervals involving Time Stamps, but since your question is only pertaining to dates I will stop there.

 

[sophie123]

(date-c.svc_init_date) year(4), tenure_date ok what if the user want to see something like year and days or year and month
With days rounded up or down.

Cosmetically the user want the column named tenure and the data to be 1year 25days or 1 year and 1 month
Thanks

 

[tdatgod]

year(4) to Month is a valid interval, just use it.

year(4) to Day isn't. you have to do that in 2 steps.....

first calculate years and then subtract that off and subtact days from what remains.

sel
 Current_date Today,
        date '2002-09-23' in_service_date,
   ((Today - in_service_date) year(4)) my_years,
   ((Today - my_years) - in_service_date) my_days
;

 *** Query completed. One row found. 4 columns returned.
 *** Total elapsed time was 1 second.

   Today  in_service_date  my_years      my_days
--------  ---------------  --------  -----------
05/02/04         02/09/23         2          134

Also rounding Down is the default, so if you wanted rounding up you would have to account for it.

Maybe you want the calculation based upon the first of the month instead of today.

sel
 Current_date - extract ( day from current_date) + 1 First_of_the_month,
        date '2002-09-23' in_service_date,
   ((First_of_the_month - in_service_date) year(4)) my_years,
   ((First_of_the_month - my_years) - in_service_date) my_days
;

 *** Query completed. One row found. 4 columns returned.
 *** Total elapsed time was 1 second.

First_of_the_month  in_service_date  my_years      my_days
------------------  ---------------  --------  -----------
          05/02/01         02/09/23         2          131

Maybe you want the calculation based upon the first of NEXT month instead of today.

sel
 (Current_date - extract ( day from current_date) + 1) +
           Interval '1' Month First_of_next_month,
        date '2002-09-23' in_service_date,
   ((First_of_next_month - in_service_date) year(4)) my_years,
   ((First_of_next_month - my_years) - in_service_date) my_days
;

 *** Query completed. One row found. 4 columns returned.
 *** Total elapsed time was 1 second.

First_of_next_month  in_service_date  my_years      my_days
-------------------  ---------------  --------  -----------
           05/03/01         02/09/23         2          159

Different things you can do by just playing with the Intervals and the calculations.