I have a timestamp(6) field which holds values like:
I need to get this timestamp as:
I want to avoid SUBSTR and alike fucntions and do this in one shot. Is not there any timestamp format though which we can accomplish this?
what i understand from ur question is, u want to display a timestamp like 2011-09-08 15:10:00.000000.
You could simply run the query as given in below format.
SEL TIMESTAMP '2011-09-08 15:10:00.000000'.
One way I can see is concatenate date and extract hour and concatenate with ||':00:00.000000')
Another way is
SELECT CAST(current_date AS TIMESTAMP(0)) + CAST(EXTRACT(HOUR FROMcurrent_timestamp)
AS INTERVAL HOUR(2).......);
Thanks for your input.
My field, say myDateTS, is TIMESTAMP(6). So, I guess we would not be able to cast it to TIMESTAMP(0). We can then cast it to date and then again to timestamp(0) and then the other things. I would like to avoid these many CAST operations, if we can :)
Moreover, for some comparision like below, without casting it to timestamp(6), the expression will evaluate to false.
select CASE when '2014-09-24 04:00:00.0' = '2014-09-24 04:00:00.000000' then 1
else 2 end as euki;
How about the first one?
One way I can see is concatenate date and extract hour and concatenate with ||':00:00.000000') ?
You don't like it? It is a bit dirty :)
Yeah Raja, the first idea, of conacatenating with ':00:00.000000', I had it earlier :)
Finally I am going with something like below:
SELECT CAST(CAST(myDateDTS as DATE format 'YYYY-MM-DD') AS TIMESTAMP(6))
+ CAST(EXTRACT(HOUR FROM myDateDTS) AS INTERVAL HOUR(2)) as anotherDTS
Please do comment if you have any better way of doing the same.
You could also try the below query,
SEL CAST(CAST(CURRENT_TIMESTAMP AS FORMAT 'YYYY-MM-DD b HH:MI:SS') AS TIMESTAMP(6)) - INTERVAL '1' MINUTE * (EXTRACT(MINUTE FROM (CAST(CAST(CURRENT_TIMESTAMP AS FORMAT 'HH:MI:SS') AS TIMESTAMP(6)))))