SQL Statement: Where Clause for Date Range?

Question

I'm attempting to write a WHERE clause into a SQL statement. I want to output a date range for my data b/c Excel is trying to output more data into a worksheet than it can handle. So, this is the SQL statement. I just want o display the data >01/01/2018 and I'm not successful in getting the statement to generate.

I'm getting an error: ORA-00933: SQL Command not properly ended.

SELECT "V_RELEASES_COMB"."ITEMNO",
       "V_RELEASES_COMB"."REV",
       "V_RELEASES_COMB"."DESCRIP",
       "V_RELEASES_COMB"."ORDERNO",
       "V_RELEASES_COMB"."PONO",
       "V_RELEASES_COMB"."RELEASE_QUAN",
       "V_RELEASES_COMB"."REQUEST_DATE",
       "V_RELEASES_COMB"."PROMISE_DATE",
       "V_RELEASES_COMB"."SHIP_DATE",
       "V_RELEASES_COMB"."CUSTNO",
       "V_RELEASES_COMB"."COMPANY",
       "V_RELEASES_COMB"."DAYS_DIFF",
       "V_RELEASES_COMB"."CUMM_SHIPPED",
       "V_RELEASES_COMB"."EPLANT_ID",
       "EPLANT"."COMPANY",
       "V_RELEASES_COMB"."ACTUAL_SHIPDATE"
FROM "IQMS"."V_RELEASES_COMB" "V_RELEASES_COMB"
     LEFT OUTER JOIN "IQMS"."EPLANT" "EPLANT" ON "V_RELEASES_COMB"."EPLANT_ID" = "EPLANT"."ID"
WHERE "V_RELEASES_COMB"."SHIP_DATE" > 01 / 01 / 2018
  "V_RELEASES_COMB"."CUMM_SHIPPED" > 0
  AND "V_RELEASES_COMB"."EPLANT_ID" > 79
ORDER BY "V_RELEASES_COMB"."CUSTNO";

Please consider the readability of code when writing SQL; whitespace and line breaks are important parts of writing code (I've formatted it for you). On the note of your error, that isn't a SQL Server error. I suspect something is using Oracle. Now that I've formatted your code for you though, notice there's something missing before "V_RELEASES_COMB"."CUMM_SHIPPED" > 0" — Thom A
– Thom A ♦, Commented Feb 21, 2019 at 17:07

Gordon Linoff · Accepted Answer · 2019-02-21 17:09:26Z

2

I cannot explain the problem you are getting. However, you should write the WHERE clause as:

WHERE "V_RELEASES_COMB"."SHIP_DATE" > DATE '2018-01-01' AND
      "V_RELEASES_COMB"."CUMM_SHIPPED" > 0 AND
      "V_RELEASES_COMB"."EPLANT_ID" > 79

You are comparing a date to a calculation -- 1 / 1 / 2018, instead of to a date.

answered Feb 21, 2019 at 17:09

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Pulschen · Accepted Answer · 2019-02-21 17:07:33Z

0

If "SHIP_DATE" is a date type column, you should be able to use the SQL format in the WHERE clause.

WHERE "V_RELEASES_COMB"."SHIP_DATE" > '2018-11-01'

If this does not help, can you post an example of the stored data?

answered Feb 21, 2019 at 17:07

Alex Pulschen

313 bronze badges

Comments

danhunt · Accepted Answer · 2019-02-21 17:41:11Z

0

As has been said, I think instead of writing the date like this:

01 / 01 / 2018

you should write it like this:

'01/01/2018'

edited Feb 21, 2019 at 17:41

answered Feb 21, 2019 at 17:18

danhunt

12 bronze badges

2 Comments

Markoorn Over a year ago

This answer provides no more information than the other answers already submitted - if you're going to answer you should rather propose a better/alternative solution instead of repeating what's already been said.

danhunt Over a year ago

Oh ok, I thought what I said was a little more specific than the other comment regarding the actual date format.

Collectives™ on Stack Overflow

SQL Statement: Where Clause for Date Range?

3 Answers 3

Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Related