CODEWITHSUNDEEP
sqloracle-database
ALK
ALK

Reputation: 87

SQL Timestamp Extract Time and group by

Select to_char(Contactdate,'HH24:MI:SS') 
from CHSTAGING

I have this sql query that I would like to extract the time from the timestamp. However once I use a GROUP BY it doesn't work. 

GROUP BY to_char(Contactdate,'HH24:MI:SS')

Here is an example of how the timestamp looks like in the dataset. I would like to group together the rows with the same time. ie. HH:MI:SS

Contactdate Field

Upvotes: 0

Views: 123

Answers (2)

India.Rocket
India.Rocket

Reputation: 1245

Try this :-

Select cast(contactdate as time) as Tim
from
INTERACT.UACI_CHSTAGING_MSISDN
group by cast(contactdate as time) 

OR

Select tim from
(
Select to_char(Contactdate,'HH24:MI:SS') as tim
from INTERACT.UACI_CHSTAGING_MSISDN 
) a
group by tim

Hope this helps:-)

Upvotes: 1

JohnHC
JohnHC

Reputation: 11205

Put it in a CTE if it's playing up...

with CTE as
(
  select mt.*, to_char(contactdate, 'HH24:MI:SS') as ContactTime
  from mytable mt
)
select ContactTime, count(somefield) as countedfield
from CTE
group by ContactTime

Upvotes: 1

Related Questions