CODEWITHSUNDEEP
sqlteradata
Kyle McPhillips
Kyle McPhillips

Reputation: 63

How to add a certain number of days based on field value

I'm working on creating a view to convert some values that we get coming in for dates/times. (I've figured out the times already.) In this table, dates come in the format of "Days after 12-31-1840." I'm trying to create a view that shows the actual dates/times rather than in this format. This is what I have so far:

CASE WHEN UPPER(FLWSHT_MEAS_NM) LIKE '%DATE' THEN TO_CHAR(DATE '1840-12-31' + INTERVAL ACUTE_MEASURE_VALUE DAY)

I know that this is the correct syntax for adding dates, because I'm able to get the view working with this instead:

CASE WHEN UPPER(FLWSHT_MEAS_NM) LIKE '%DATE' THEN TO_CHAR(DATE '1840-12-31' + INTERVAL '30' DAY)

My question is, how do I add a specific number of days based on the ACUTE_MEASURE_VALUE field? I'm not able to run the code to get a runtime error as it's coming up as a syntax error.

Upvotes: 1

Views: 2304

Answers (1)

Chris Maurer
Chris Maurer

Reputation: 2539

From the Teradata documentation:

Teradata SQL extends the ANSI SQL:2011 standard to allow the operations of adding or subtracting a number of days from an ANSI DATE value. Teradata SQL treats the number as an INTERVAL DAY value.

I'm assuming your field ACUTE_MEASURE_VALUE is already in your table and is an integer. The words INTERVAL and DAY are part of the specification of interval constants - this is a variable and syntactically you don't use those keywords.

...TO_CHAR(DATE '1840-12-31' + ACUTE_MEASURE_VALUE)...

Just drop the INTERVAL keyword and the DAY keyword and it should work.

By the way, why are you using To_Char() in this? By transforming it into a character string it preempts anyone using this view from performing calculations on this date. If you leave the view in DATE format then any subsequent Select from this view has a lot more flexibility in manipulating this data field.

Upvotes: 2

Related Questions