×
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

Conveting a char to date

thread318-1792197

Conveting a char to date

Conveting a char to date

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

RE: Conveting a char to date

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

RE: Conveting a char to date

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

RE: Conveting a char to date

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
http://www.mirtheil.com

RE: Conveting a char to date

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

RE: Conveting a char to date

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

RE: Conveting a char to date

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



RE: Conveting a char to date

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
http://www.mirtheil.com

RE: Conveting a char to date

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

RE: Conveting a char to date

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
http://www.mirtheil.com

RE: Conveting a char to date

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

RE: Conveting a char to date

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
http://www.mirtheil.com

RE: Conveting a char to date

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

RE: Conveting a char to date

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
http://www.mirtheil.com

RE: Conveting a char to date

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

RE: Conveting a char to date

Quote:

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

RE: Conveting a char to date

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

RE: Conveting a char to date

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

RE: Conveting a char to date

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

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