Archives of the TeradataForum
Message Posted: Wed, 07 Sep 2011 @ 09:36:21 GMT
| Subj: | | Re: Hours Subtraction |
| |
| From: | | Dieter Noeth |
Anomy.Anom wrote:
| | I have attribute with TRANSTime Decimal(6). However the value in that are six hour more than the time, suppose my time is 19:30:42 then it
stored as 253042. Besides to the TRANSTIME I have a valid date now I want to convert it to equivalent Timestamp. with -6 hours. I am finding
difficult to convert as the input DataType is Decimal(6). | |
Of course a question comes to mind: Why is that "time" stored in such a strange way?
I would prefer the following as it's easy to understand:
CAST(TRANSDATE AS TIMESTAMP(0))
+ CAST(transtime / 10000 AS INTERVAL HOUR) - INTERVAL '6' HOUR
+ CAST(transtime / 100 MOD 100 AS INTERVAL MINUTE)
+ CAST(transtime MOD 100 AS INTERVAL SECOND)
Or a bit shorter:
CAST(TRANSDATE AS TIMESTAMP(0))
+ CAST((transtime - 60000) / 10000 AS INTERVAL HOUR)
+ CAST(transtime / 100 MOD 100 AS INTERVAL MINUTE)
+ CAST(transtime MOD 100 AS INTERVAL SECOND)
Dieter
| 
