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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Conveting a char to date 1

Status
Not open for further replies.

Papagovas

Programmer
Nov 13, 2017
15
CA
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
 
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
 
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
 
If the format is going to stay in the "YYYY/MM/DD" format, you can use REPLACE to change the "/" to "-" like:
Code:
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
 
I tried your sample with out the Select in my query and it did not convert.
Code:
 convert(replace('2018/01/18', '/','-'), SQL_DATE)]
I also tried this
Code:
 convert(replace(ShipDate, '/','-'), SQL_DATE)]
and I received the Error [LNA][PSQL][SQL Engine]Error in expression: convert ( REPLACE ( ShipDate , '/' , '-' ) , SQL_DATE )

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



 
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:
Code:
select replace('2018/01/18','/','-')


Mirtheil
 
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

Code:
 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'
 
As a test, what do you get if you run this query:
Code:
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
 
I continue to get an error in expression.
[LNA][PSQL][SQL Engine]Error in expression: IF ( ShipDate <> '' , convert ( REPLACE ( ShipDate , '/' , '-' ) , SQL_DATE ) , NULL )
 
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:
Code:
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
 
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
 
What I'm asking is what does this value look like:
Code:
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
 
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.
 
Doesn't Pervasive have a function that could check if a value is a date or not, Like the ISDATE function in VB.Net.
 
Thank you Mirtheil
With your Help I have it solved it.
Here is the Updated code
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top