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!

Register Log in
  • Congratulations bkrike 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

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
 
Sort by date Sort by votes
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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'



 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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'
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
I continue to get an error in expression.
[LNA][PSQL][SQL Engine]Error in expression: IF ( ShipDate <> '' , convert ( REPLACE ( ShipDate , '/' , '-' ) , SQL_DATE ) , NULL )
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Doesn't Pervasive have a function that could check if a value is a date or not, Like the ISDATE function in VB.Net.
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dgillz
  • Locked
  • Question
How to Execute Create View code in Actian Zen?
Replies
1
Views
527
mirtheil
mirtheil
profoundhypnotic
  • Locked
  • Question
How to read a csv file in Pervasive?
Replies
1
Views
582
mirtheil
mirtheil
profoundhypnotic
  • Locked
  • Question
how to select min and max value and the date for each
Replies
1
Views
526
mirtheil
mirtheil
Ino Mart
  • Locked
  • Question
How to use a variable as table name in a procedure 1
Replies
2
Views
442
Ino Mart
Ino Mart
ianbtheog
  • Locked
  • Question
Connection Manager is not running at the specified port number - Pervasive 13.31.014 - Intermittent
Replies
2
Views
548
ianbtheog
ianbtheog

Part and Inventory Search

Sponsor

Back
Top