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!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Expression evaluation error 1

Status
Not open for further replies.

Papagovas

Programmer
Nov 13, 2017
15
CA
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
 
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
 
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
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top