Conveting a char to date 1

[Papagovas]

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, 29, 10)) AS ShipDate,
The IF function works fine.
My question is how to convert the ship date to a Date.
I tried using convert(Shipdate,SQL_DATE) but I get an error in the convet function.

Thanks

 

[mirtheil]

What's the error? What is the actual value of Shipdate? The standard date format is 'yyyy-mm-dd'. You might need to change your value from 'yyyy/mm/dd' to the 'yyyy-mm-dd'.


Mirtheil

http://www.mirtheil.com

 

[Papagovas]

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

 

[mirtheil]

If the format is going to stay in the "YYYY/MM/DD" format, you can use REPLACE to change the "/" to "-" like:

select convert(replace('2018/01/18', '/','-'), SQL_DATE)

If however the format changes, you'll need to write the appropriate conversion to get it into YYYY-MM-DD.

Mirtheil

http://www.mirtheil.com

 

[Papagovas]

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 , '/' , '-' ) , SQL_DATE )

Thanks

 

[mirtheil]

What do you mean you tried it "with out the Select"? What was your query? You can't just execute a convert without a Select. Convert is a function that's used in the select list or the where clause.
Did the query I posted (that doesn't rely on any table) work?
Can you edit your last post to fix the code section so that what you tried shows correctly?


Mirtheil

http://www.mirtheil.com

 

[Papagovas]

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'

 

[mirtheil]

No, my query converts '2018-01-18' to a Date data type. The 1/18/2018 in PCC is correct because that's how PCC displays date fields.
Both Convert and Cast require a string in the format of 'YYYY-MM-DD' to convert/cast to a date. All other string formats are invalid.

The portion of the query that converts the "/" to "-" is:

select replace('2018/01/18','/','-')

Mirtheil

http://www.mirtheil.com

 

[Papagovas]

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
  Process_LOG.Process,
  Process_LOG.OE,
If(Substring(shp.Shiped, 29, 10) = '20__/__/__', '', Substring(shp.Shiped, 29, 10)) AS ShipDate,
IF(ShipDate<>'',convert(replace(ShipDate, '/','-'), SQL_DATE),'')as CvrtDate
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'

 

[mirtheil]

As a test, what do you get if you run this query:

SELECT
  Process_LOG.Process,
  Process_LOG.OE,
If(Substring(shp.Shiped, 29, 10) = '20__/__/__', '', Substring(shp.Shiped, 29, 10)) AS ShipDate,
IF(ShipDate<>'',convert(replace(ShipDate, '/','-'), SQL_DATE),null)as CvrtDate
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'

Mirtheil

http://www.mirtheil.com

 

[Papagovas]

I continue to get an error in expression.
[LNA][PSQL][SQL Engine]Error in expression: IF ( ShipDate <> '' , convert ( REPLACE ( ShipDate , '/' , '-' ) , SQL_DATE ) , NULL )

 

[mirtheil]

And what does ShipDate look like if you don't try to convert or replace values in it? What about if you don't convert it like:

IF(ShipDate<>'',replace(ShipDate, '/','-'),null)as CvrtDate

Something in your data is causing this error. If my original query with the convert and replace works, then it is something in your data.
Mirtheil

http://www.mirtheil.com

 

[Papagovas]

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

 

[mirtheil]

What I'm asking is what does this value look like:

If(Substring(shp.Shiped, 29, 10) = '20__/__/__', '', Substring(shp.Shiped, 29, 10)) AS ShipDate,

Because in some cases you're setting the value to '' (and empty string). An empty string cannot be converted to a date. Null however can be converted. If your IF statement is generating an empty string that is then converted into a date, it will fail.

Mirtheil

http://www.mirtheil.com

 

[Papagovas]

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.

 

[mirtheil]

An empty string cannot be converted to a date. Null however can be converted. If your IF statement is generating an empty string that is then converted into a date, it will fail.

As stated in a previous post.

Mirtheil

http://www.mirtheil.com

 

[Papagovas]

Doesn't Pervasive have a function that could check if a value is a date or not, Like the ISDATE function in VB.Net.

 

[mirtheil]

No. Pervasive does not have an IsDate function. If you change your query to return NULL instead of an empty string, your query should work.

Mirtheil

http://www.mirtheil.com

 

[Papagovas]

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 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'

There were two columns that needed to be checked for null Values.
shp.Shiped and the shippedDate.