.TO_CHAR problem

On Thu, 15 Feb 2001, Guillaume [ISO-8859-1] L�mery wrote:

Hi all,

I want to have some stats from my data grouped by 2hours periodes.

Here is the query :

SELECT TO_DATE(TO_CHAR (date_clic,'DD-MM-YYYY ')
|| (TO_CHAR(
TO_NUMBER(
TO_CHAR(date_clic,'hh24')
, 99)
- mod(
TO_NUMBER(
TO_CHAR(date_clic,'hh24')
, 99)
,2)
, 'hh24')
)
||':00','DD-MM-YYYY HH24:MI') AS date_debut
FROM clic_campagne;

But I only get the date whithout the hours and the minute, how can I get
them ?

As you say "..get the date...", hours+minutes+ contains timestamp.

You must call SELECT TO_TIMESTAMP(...) instead TO_DATE(...).

BTW, I not sure if your TO_NUMBER() is needful, less expensive will
probably TO_CHAR(date_clic,'hh24')::int.

Karel