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
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
Mirtheil
http://www.mirtheil.com
RE: Conveting a char to date
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
CODE
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
CODE
I also tried this
CODE
Thanks
RE: Conveting a char to date
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
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
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
Mirtheil
http://www.mirtheil.com
RE: Conveting a char to 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
RE: Conveting a char to date
CODE
Mirtheil
http://www.mirtheil.com
RE: Conveting a char to date
[LNA][PSQL][SQL Engine]Error in expression: IF ( ShipDate <> '' , convert ( REPLACE ( ShipDate , '/' , '-' ) , SQL_DATE ) , NULL )
RE: Conveting a char to date
CODE
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
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
CODE
Mirtheil
http://www.mirtheil.com
RE: Conveting a char to date
And one of your convertions was chanking for empty values and stillsfailed.
RE: Conveting a char to date
Mirtheil
http://www.mirtheil.com
RE: Conveting a char to date
RE: Conveting a char to date
Mirtheil
http://www.mirtheil.com
RE: Conveting a char to date
With your Help I have it solved it.
Here is the Updated code
CODE
There were two columns that needed to be checked for null Values.
shp.Shiped and the shippedDate.