pervasivepervasive-sql

'[pervasive][ODBC Engine Interface]Invalid date, time or timestamp value' When trying to compare dates

I cant get past this error.

I have a date field stored in mmddyy format.

I want to only return records with a date after 12/31/2019.

I get that Pervasive uses yyyy-mm-dd by default.

I am reformatting the date with this code:

Convert(Concat('20',Concat(Right(Date,2),Concat('-',Concat(Left(Date,2),Concat('-',Substring(Date)))))),SQL_DATE)

A date value of 081820 returns 44061.

Convert('2019-12-31',SQL_DATE)

returns 43830.

And Convert(Concat('20',Concat(Right(Order_Header.Date_Shipped,2),Concat('-',Concat(Left(Order_Header.Date_Shipped,2),Concat('-',Substring(Order_Header.Date_Shipped,3,2)))))),SQL_DATE) > Convert('2019-12-31',SQL_DATE)

returns

[pervasive][ODBC Engine Interface]Invalid date, time or timestamp value

Ive tried cast and convert... anyone know what I am doing wrong in my And statement (in my where clause) or how I can get around this error?

Solution

  • You might have values that are not valid for a date in your data. You should probably check the records. The Date specification requires a month of 1 to 12, a day of 1 to 31 depending on the month (so Feb 30 would be invalid), and a year of 0000 to 9999. Something like this for months, you'd want to check days as well:

    insert into sodate (f1) values ('442020');
insert into sodate (f1) values ('  2020');
select f1, left(f1,2) from sodate where left(f1,2) not in ('01','02','03','04','05','06','07','08','09','10','11','12')

    The substring in the first example is missing parameters. Once you've got the date in the right format ('yyyy-mm-dd') you should be able to compare it to a date value without converting it to the SQL_DATE data type. One more thing, you're prepending '20' before all years, what happens if your data has dates from pre-2000? You'll need to account for that. In the PCC, when I run:

    select Convert('2019-12-31',SQL_DATE)

    I get:

        EXPR_1
==========
12/31/2019

    I used the folowing:

    create table sodate (f1 char(6));
insert into sodate (f1) values ('123119');
insert into sodate (f1) values ('081818');
insert into sodate (f1) values ('081820');
insert into sodate (f1) values ('082020');
select * from sodate;

-- This should give the results you want
select concat(concat(concat(concat(concat('20',right(f1, 2)),'-'),left(f1,2)),'-'),substring(f1,3,2)) from sodate where
concat(concat(concat(concat(concat('20',right(f1, 2)),'-'),left(f1,2)),'-'),substring(f1,3,2)) > '2019-12-31';
-- This query shows you don't need the CONVERT to compare a date.  
select concat(concat(concat(concat(concat('20',right(f1, 2)),'-'),left(f1,2)),'-'),substring(f1,3,2)) from sodate where

    concat(concat(concat(concat(concat('20',right(f1, 2)),'-'),left(f1,2)),'-'),substring(f1,3,2)) >= now()

    And got the following results:

    EXPR_1        
==============
2019-12-31    
2020-08-18

    and the second query gives:

    EXPR_1        
==============
2020-08-20