×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Expression evaluation error

thread318-1792260

Expression evaluation error

Expression evaluation error

(OP)
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

RE: Expression evaluation error

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

RE: Expression evaluation error

(OP)
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

RE: Expression evaluation error

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

RE: Expression evaluation error

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close