Expression evaluation error 1

[Papagovas]

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.

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]

So simply adding

 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]

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]

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]

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 .

 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.