Expression evaluation error 1

Papagovas · Jan 21, 2019

Hi
I'm using PCC V10
I have this query that works fine, until I Query for dates greater or equal to a certain date,which I get an Expression evaluation error.
I also get the same Error when checking for values is not null.

Code:

SELECT
  Process_LOG.Process,
  Process_LOG.OE,
   IF(shp.Shiped is null,NUll,If(Substring(shp.Shiped, 29, 10) = '20__/__/__', null, convert(replace(Substring(shp.Shiped, 29, 10),'/','-'),SQL_DATE))) as ShipDate
FROM 
  SCHEDULELOG.Process_LOG
LEFT JOIN (SELECT
      msk.ORD_ORDER_NO AS num,
      msk.FIELD_NAME,
      msk.MASK AS Shiped
    FROM
      CWLIVEWB.ORDE_WB_MASK AS msk
    WHERE
      LTrim(msk.FIELD_NAME) = 'Install') shp ON  shp.num = oe
 WHERE  Process_LOG.Department='Shipping'
  and shipdate >= '2018-12-01'

When tried "shipdate" >= Convert('2018-12-01',SQL_DATE) I received the same error

Thanks

mirtheil · Jan 22, 2019

So simply adding

Code:

 and shipdate >= '2018-12-01'

causes the error?
If so, it is probably another invalid date being found and you'll need to find the value that's failing. Take off the conversions and look at the results. See if you find a value that's not in the proper format and isn't a valid date. A valid date is one that has a month of 1 to 12. Day of 1 to 31 dependent on the month (Feb 31 is invalid), and a year of 0000 to 9999.

Mirtheil

http://www.mirtheil.com

Papagovas · Jan 26, 2019

The date format changes with the conversions.
The result with the conversions is MM/dd/yyyy and with out the Convertion it is yyyy/MMM/dd.
Also if I understand correct the replacement function is supose to change the YYYY/MM/DD to YYYY-MM-DD but it doesn't it displays MM/dd/yyyy

mirtheil · Jan 26, 2019

Converting 'MM/dd/yyyy' into 'YYYY-mm-dd' would be different than converting 'YYYY/MM/dd' to 'yyyy-mm-dd'. You need to write your statement to take the format of the original date before converting so that you can convert it properly.

Mirtheil

http://www.mirtheil.com

Papagovas · Jan 26, 2019

Solved
I'm not too worried about the format.
It's that I could'nt use the convert shipdate in the where clause without the error.
I added the the not converted line in the where clause and it runs OK .

Code:

 SELECT
  Process_LOG.Process,
  Process_LOG.OE,
   IF(shp.Shiped is null,NUll,If(Substring(shp.Shiped, 29, 10) = '20__/__/__', null, convert(replace(Substring(shp.Shiped, 29, 10),'/','-'),SQL_DATE))) as ShipDate,
   IF(shp.Shiped is null,NUll,If(Substring(shp.Shiped, 29, 10) = '20__/__/__', null,Substring(shp.Shiped, 29, 10))) as ShipDate_new
FROM 
  SCHEDULELOG.Process_LOG
LEFT JOIN (SELECT
      msk.ORD_ORDER_NO AS num,
      msk.FIELD_NAME,
      msk.MASK AS Shiped
    FROM
      CWLIVEWB.ORDE_WB_MASK AS msk
    WHERE
      LTrim(msk.FIELD_NAME) = 'Install') shp ON  shp.num = oe
 WHERE  Process_LOG.Department='Shipping'
  and shipdate_new > '2018/12/10' and shipdate_new < '2018/12/30'
  order by shipdate_new desc

I'm curious now, If the ShipDate_new is a char column with dates, I showldn't be able to search the column as dates, but it is treating it as dates.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Expression evaluation error 1

Papagovas

Programmer

mirtheil

Programmer

Papagovas

Programmer

mirtheil

Programmer

Papagovas

Programmer

Similar threads

Part and Inventory Search

Sponsor