CODEWITHSUNDEEP
sqloracle-databasedatedate-format
VSM
VSM

Reputation: 23

Oracle - truncating time from a timestamp field

I'm looking to get a count of email click rates by day however I cannot figure out the starting structure of the query. Can someone please help? I'm learning SQL and this website has been a great resource for me. Thank you!

SELECT TRUNC(EVENTTIMESTAMP, DATE) as DATE, COUNT(*)
FROM EMAILCLICK
GROUP BY DATE

Example of EVENTTIMESTAMP = 02-JAN-20 02.32.50.462000000 PM

Upvotes: 1

Views: 314

Answers (2)

Hung Le
Hung Le

Reputation: 151

Hope will help you.

select TRUNC(EVENTTIMESTAMP) as "DATE", COUNT(*)
from EMAILCLICK
GROUP BY TRUNC(EVENTTIMESTAMP)

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

There are 3 issues in your query:

  1. Wrong syntax for TRUNC(EVENTTIMESTAMP, DATE)
  2. Missing TRUNC in GROUP BY clause. You cannot use alias name in group by clause, you must mention the same expression used in the SELECT.
  3. DATE is a reserved keyword in Oracle.

You need to use correct syntax:

SELECT TRUNC(EVENTTIMESTAMP) as "DATE", COUNT(*)
FROM EMAILCLICK
GROUP BY TRUNC(EVENTTIMESTAMP);

as DATE

Remember, DATE is a reserved keyword in Oracle. You should either use a proper alias name or use it as quoted identifier by enclosing within double-quotation marks ".

Upvotes: 3

Related Questions