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...
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
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...
Thank you Mirtheil
With your Help I have it solved it.
Here is the Updated 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...
The Value looks like '2018/04/11' in the following format YYYY/MM/dd and others are empty string
And one of your convertions was chanking for empty values and stillsfailed.
The data in the sh.Shiped Looks like this 'Y/N _ SHIP (20YY/MM/DD) 20__/__/__'.
After extracting the date wich is the ShipDate some rows are empty and the others Look like this '2018/04/11' YYYY/MM/dd
I continue to get an error in expression.
[LNA][PSQL][SQL Engine]Error in expression: IF ( ShipDate <> '' , convert ( REPLACE ( ShipDate , '/' , '-' ) , SQL_DATE ) , NULL )
Im Still getting the error [LNA][PSQL][SQL Engine]Error in expression: IF ( ShipDate <> '' , convert ( REPLACE ( ShipDate , '/' , '-' ) , SQL_DATE ) , '' )
I should let you know not all rows have dates in them etheir they are blank (not null) or dates.
This is my query
SELECT...
Sorry I didn't explain my self propertly.
I did used in a Select statement.
If I understanded you correctly your code is supose to convert '2018/01/18' to '2018-01-18'.
Instead the output in the PCC was 1/18/2018'
I tried your sample with out the Select in my query and it did not convert. convert(replace('2018/01/18', '/','-'), SQL_DATE)]
I also tried this convert(replace(ShipDate, '/','-'), SQL_DATE)] and I received the Error [LNA][PSQL][SQL Engine]Error in expression: convert ( REPLACE ( ShipDate ...
Thanks Mirthell for your reply.
The error I received is [LNA][PSQL][SQL Engine]Error in expression: Convert ( ShipDate , SQL_DATE )
How would I go about converting the date to yyyy-mm-dd
Hi
I'm using pcc v10
I have a column Char that has this data like this 'Y/N _ SHIP (20YY/MM/DD) 20__/__/__', (default) or Y/N y SHIP (20YY/MM/DD) 2019/01/17' .
I'm pulling just the date using the if function.
If(Substring(shp.Shiped, 29, 10) = '20__/__/__', NULL, Substring(shp.Shiped...
Hi Finely have it solved by understanding how the tables were indexed.
By joining the tables with the indexed columns, it executes quick and I don't get any time out errors.
Thanks Mirtheil for your quick reply.
First of all this still JRCharlie I had to create a new account because I was unable to login nor reset my password.
1st Im currently using ver12
2nd This query returns 200 records out of 49000
3rd OEli_WB_mask has 3 keys
KEY0...
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.