Casting Interval Day(4) to Minute issues

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Highlighted
Enthusiast

Casting Interval Day(4) to Minute issues

Hello Teradata friends. I have a column that is an Interval Day(4) Data type. I need to cast this collumn as a date mm/dd/yy. Is this possible? I have tried searching online and I can't find anything. I have to do some date calculations beteween 3 different collumns. Two of them are date fields and this one is not.

 

I have tried the following and it does not work

SyntaxEditor Code Snippet

Cast( EE.Date1_field as date)  - cast(EE.date2_fields as date) - cast(EE.date3_field as date)
However if I comment out the 3rd cast it works just fine:
SyntaxEditor Code Snippet
Cast( EE.Date1_field as date)  - cast(EE.date2_fields as date) -- cast(EE.date3_field as date)
Date1 is a timestamp and Date2 is also a timestamp, and as mentioned earlier date3 is an interval day(4)
  

 


Accepted Solutions
Senior Apprentice

Re: Casting Interval Day(4) to Minute issues

No problem.

 

Fred makes a good point about using EXTRACT to get just the day portion - one less CAST to perform.

 

From what you said your 'date1' and 'date2' fields are already timestamps, so there is no need to convert those columns directly. Try the following:

EXTRACT(DAY FROM ((date1-date2 DAY(4) TO MINUTE) - date3))

This will return an integer value which is the number of days from the calculation.

 

This will truncate the result to return the integer value, there is no rounding involved, but at least this is only a single truncation. I don't know if that is significant to you.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
9 REPLIES
Senior Apprentice

Re: Casting Interval Day(4) to Minute issues

Hi,

 

No you cannot do what you've described.

 

An 'interval day' data type represents a number of days.A 'date' data type represents a single date.

 

These are fundamentally different things. Is this what you're really trying to do?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Senior Apprentice

Re: Casting Interval Day(4) to Minute issues

In addition to what I said above, you can:

- add/subtract an INTERVAL DAY column to/from a DATE or TIMESTAMP column. The result will be a new date or timestamp.

 

Also, just to be clear, Teradata has both DATE and TIMESTAMP data types and they are different.

- a DATE column holds only a date. There is no time portion to it.

- a TIMESTAMP column holds both a date and a time.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Casting Interval Day(4) to Minute issues

SyntaxEditor Code Snippet

Cast( EE.Date1_field as date)  - cast(EE.date2_fields as date) - cast(EE.date3_field as date)

Dave with that being said with the query above how could I subrtract Date3 from the other collumns? Thats all I am trying to do, I thought I had to cast it. Date1 and date2 are time stamp fields and date3 is the interval day collumn.

Senior Apprentice

Re: Casting Interval Day(4) to Minute issues

Hi,

 

Use CAST, but cast your INTERVAL column as an INTEGER, something like:

Cast( EE.Date1_field as date)  - cast(EE.date2_fields as date) - cast(EE.date3_field as INTEGER)

You could probably use any of the numeric data types that will hold a big enough value (SMALLINT, DECIMAL(4,0) etc.).

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Casting Interval Day(4) to Minute issues

Dave,

 

I tried that and I am getting Invalid opperation for DateTime or Interval

Senior Apprentice

Re: Casting Interval Day(4) to Minute issues

Hi,

Sorry, I mis-read your original post.

You can use a double CAST, first to DAY(4) and then to INTEGER, such as:

 

CAST( CAST(EE.date3_field AS INTERVAL DAY(4)) AS INTEGER)

I think the above will allow the SQL to run.

 

But it may or may not give you the accuracy that you need.The above code will for example convert '15 23:59' (15 days, 23 hours and 59 minutes) to 23 days, losing almost a full day which may or may not be significant.

 

Having said that, you're already truncating the time portion of 'field1' and 'field2' by converting timestamp columns to dates.

 

If accuracy is an issue, maybe you should do all calculations using timestamps and then convert the final result to an integer value which would be a number of days. Up to you, but maybe worth considering.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Casting Interval Day(4) to Minute issues

Dave thank you your suggestion worked, However I do have one final question. How would I calculate all these fields as time stamps and then convert to an int? I appreciate your help here I am not trying to pawn my work off on any one here I am just out of my leauge here

Teradata Employee

Re: Casting Interval Day(4) to Minute issues

If "Date3" is already just INTERVAL DAY(4) then you don't need to CAST at all, just use it in the calculation.

If it is actually INTERVAL DAY(4) TO MINUTE, then use EXTRACT(DAY FROM Date3) to get only the whole days portion of the interval as an INTEGER.

Senior Apprentice

Re: Casting Interval Day(4) to Minute issues

No problem.

 

Fred makes a good point about using EXTRACT to get just the day portion - one less CAST to perform.

 

From what you said your 'date1' and 'date2' fields are already timestamps, so there is no need to convert those columns directly. Try the following:

EXTRACT(DAY FROM ((date1-date2 DAY(4) TO MINUTE) - date3))

This will return an integer value which is the number of days from the calculation.

 

This will truncate the result to return the integer value, there is no rounding involved, but at least this is only a single truncation. I don't know if that is significant to you.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com