How to GROUP by DATE with same DATE but different time?

Question

How to GROUP by DATE with same DATE but different time ?

I using this code but getting error :

SELECT TO_CHAR(TO_DATE(FQA_START_DATE,'DD-Mon-YYYY HH24:MI:SS'),'YYYY-MM-DD') DAY, DE_NO
FROM (
 SELECT TO_CHAR(TO_DATE(FQA_START_DATE,'DD-Mon-YYYY HH24:MI:SS'),'YYYY-MM-DD') DAY, DE_NO
 FROM PACKINGAPPS_FQA
WHERE (TO_CHAR(TO_DATE(FQA_START_DATE,'DD-Mon-YYYY HH24:MI:SS'),'YYYY-MM-DD') BETWEEN '$start_date' AND '$end_date')
)
GROUP BY DAY, DE_NO

The error is :

Warning: oci_execute() [function.oci-execute]: ORA-00904: "FQA_START_DATE": invalid identifier in...

Please help to advice. Thanks

possibly duplicate of http://stackoverflow.com/questions/6054144/how-can-i-group-by-date-time-column-without-taking-time-into-consideration — Deepika Janiyani, Oct 22 '13 at 10:37
The error refers to the `FQA_START_DATE` column, are you sure that it's correct ? BTW, what is the data type of `FQA_START_DATE` ? — A.B.Cade, Oct 22 '13 at 10:38
@A.B.Cade data type I using varchar. So any advice for that ? — Kongkow, Oct 22 '13 at 10:39
As for the error - you've got an [answer](http://stackoverflow.com/a/19515544/1083652). As to how to remove the time part you can either use `TRUNC` (to make the time 00:00:00) after converting the string to a date or use `SUBSTR` on the original string. Anyway, I wouldn't store dates in varchar2s — A.B.Cade, Oct 22 '13 at 11:17

score 1 · Answer 1 · edited Oct 22 '13 at 10:39

1

try using

GROUP BY CAST(datefield AS DATE)

edited Oct 22 '13 at 10:39

Code Lღver

15,573
16
56
75

answered Oct 22 '13 at 10:36

Deepika Janiyani

1,487
9
17

valex · Answer 2 · 2013-10-22T10:44:39.960

1

You inner query doesn't have FQA_START_DATE in the fields list so your first SELECT is incorrect.

Try this

SELECT DAY, DE_NO
FROM (
 SELECT TO_CHAR(TO_DATE(FQA_START_DATE,'DD-Mon-YYYY HH24:MI:SS'),'YYYY-MM-DD') 
   DAY, 
   DE_NO
 FROM PACKINGAPPS_FQA
WHERE (TO_CHAR(TO_DATE(FQA_START_DATE,'DD-Mon-YYYY HH24:MI:SS'),'YYYY-MM-DD') 
         BETWEEN '$start_date' AND '$end_date')
)
GROUP BY DAY, DE_NO

edited Oct 22 '13 at 10:44

answered Oct 22 '13 at 10:39

valex

23,966
7
43
60

For DAY, it's not a column. I using DAY for AS. – Kongkow Oct 22 '13 at 10:41
Yes but your main query doesn't know `FQA_START_DATE` field it sees only DAY and DE_NO fields from your inner query. – valex Oct 22 '13 at 10:42
OK, now how can I group by DATE ? With convert date from DD-Mon-YYYY HH24:MI:SS to YYYY-MM-DD? – Kongkow Oct 22 '13 at 10:48
@Kongkow Your `Day` field is already a date so just group by `Day` – valex Oct 22 '13 at 11:21

score 0 · Answer 3 · edited Oct 22 '13 at 10:43

0

SELECT * FROM TABLE_NAME 
  WHERE DATE(coloumn_name)='SPECIFY DATE' 
  GROUP BY coloumn_name

edited Oct 22 '13 at 10:43

Code Lღver

15,573
16
56
75

answered Oct 22 '13 at 10:41

Lavneet

516
5
19

score 0 · Answer 4 · edited Oct 22 '13 at 10:43

0

you can use

SELECT field_name(s) FROM table_name GROUP BY DATE(date_field);

Here Date() function will case datetime to date.

date-and-time-functions.

edited Oct 22 '13 at 10:43

Code Lღver

15,573
16
56
75

answered Oct 22 '13 at 10:41

Bhawani Shankar Mahapatra

84
4

GolezTrol · Answer 5 · 2013-10-22T11:43:23.057

You can get the date part of a date (without time), by using the TRUNC function. TRUNC will remove the time part.

The resulting value is still a date and you can use it to group by and format it as will.

I must admit that it's not exactly clear to me what type of value the field FQA_START_DATE is and what type the input variables are. It looks like they are all strings, but I would advise you to store dates as dates and also use date input parameters if possible.

So ideally, your query would look like this:

SELECT 
  FQA_START_DATE_WITHOUT_TIME, 
  DE_NO
FROM (
 SELECT 
   trunc(FQA_START_DATE) as FQA_START_DATE_WITHOUT_TIME, 
   DE_NO
 FROM P
   ACKINGAPPS_FQA
 WHERE 
   trunc(FQA_START_DATE) BETWEEN $start_date AND $end_date
)
GROUP BY 
  FQA_START_DATE_WITHOUT_TIME, 
  DE_NO

And if you are not going to need any other fields, extra joins or aggregations, you can simplify it to this:

SELECT DISTINCT /* Distinct will remove duplicate rows */
  trunc(FQA_START_DATE) as FQA_START_DATE_WITHOUT_TIME, 
  DE_NO
FROM P
  ACKINGAPPS_FQA
WHERE 
  trunc(FQA_START_DATE) BETWEEN $start_date AND $end_date

Note that this assumes FQA_START_DATE to be a date/datetime field, assumes start_date and end_date to be dates/datetimes and will return a field FQA_START_DATE_WITHOUT_TIME, which is also a date field.

score 0 · Answer 6 · answered Oct 22 '13 at 14:43

First of all, you don't have any column with an alias FQA_START_DATE in your inline view (the SELECT statement in the FROM clause). You may have a column with that name in your base table, but that doesn't count here as you are SELECTing from an inline view and not a base table.

Second, the WHERE condition in your inline view has gone completely berserk and it will return you all the wrong results as you are comparing a character value to be between two character values.

What I would instead suggest is you use the following query:

  SELECT TRUNC(TO_DATE(FQA_START_DATE,'DD-Mon-YYYY HH24:MI:SS')) AS dt, de_no
    FROM packingapps_fqa
   WHERE TRUNC(TO_DATE(FQA_START_DATE,'DD-Mon-YYYY HH24:MI:SS'))
         BETWEEN TRUNC (TO_DATE('$start_date')) AND TRUNC(TO_DATE('$end_date'))
GROUP BY TRUNC(TO_DATE(FQA_START_DATE,'DD-Mon-YYYY HH24:MI:SS')), de_no;

How to GROUP by DATE with same DATE but different time?

6 Answers6