1

I have a requirement such that if I enter a timestamp(yyyy-mm-dd hh:mm:ss) say

2015-04-05 16:45:12

I need it converted to

2015-04-05 16:00:00

Is there an elegant way to do this in Teradata rather than extracting hour and then casting it with date?

haldar55
  • 59
  • 1
  • 1
  • 11
  • Use `SUBSTR` to get the string till `2015-04-05 16:` and then concatenate `00:00` to it. If you want, convert this back to timestamp, so now you would have the minutes and seconds set to zero. – Lalit Kumar B Apr 24 '15 at 11:40

2 Answers2

1

Instead of casting from/to a string two times it's probably more efficient to substract intervals:

ts - (EXTRACT(MINUTE FROM ts) * INTERVAL '01' MINUTE)
   - (EXTRACT(SECOND FROM ts) * INTERVAL '01' SECOND)

If this is to much code simply put it in a SQL UDF.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
  • I tried this, but the thing is intervals need to be some kind of character, whereas i would need to remove whatever minute or second that has come in. Using variable here doesn't work, even the varchar kind, I tried that. – haldar55 Apr 24 '15 at 12:40
  • This works as-is if `ts` is a timestamp, did you try it? – dnoeth Apr 24 '15 at 15:46
  • I did, but where you are using '01', i need to put variable. It doesn't work then – haldar55 Apr 27 '15 at 04:47
  • What other variable do you need? `'01' is a constant, but it's multiplied by the minute/second of the supplied timestamp. The result is a timestamp with minutes and seconds set to zero. This is exactly what you requested. – dnoeth Apr 27 '15 at 21:41
0

One way is to extract the substr you want to retain the values, and then concatenate the zeros for minutes and seconds.

For example,

SELECT concat(substr('2015-04-05 16:45:12', 1, 14), '00:00');

If you want, you could convert this back to timestamp, thus you will have the new timestamp value with the minutes and seconds value set to zero.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124