Converting Integer Float DataType to Date

[groasa]

I have little knowledge of SQL especially Pervasive, but this is what I'm trying to do:

I'm trying to create a view where the "DeliveryDate" is >= Current Year. Unfortunately, the "DeliveryDate" data type is an Integer Float in this format: "YYYYMMDD".

Does anyone know what I can do to Convert this field to a Date data type?

 

[mirtheil]

What version of Pervasive are you using? If you are using Pervasive.SQL 2000, you'll need to use the Convert function. if you are using Pervasive.SQL v7.0 or Scalable SQL v3.01 or v4.0, you can use the Mask command.

 

[groasa]

We are using Pervasive.SQL 2000i. I've created this statement:

Convert((SubString(Convert(DeliveryDate, SQL_CHAR),1,4)+ '-'+
SubString(Convert(DeliveryDate, SQL_CHAR),5,2)+'-'+
SubString(Convert (DeliveryDate, SQL_CHAR),7,2)),SQL_DATE) DeliverDate

The SQL statement works and executes correctly, but then when I try to create a View with this Convert function in the Where clause, it tells me that it is an invalid parameter.

Any clues?

 

[Guest_imported]

groasa: I work in the Pervasive engineering department. We tried to reproduce your problem on our latest build of Pervasive.SQL 2000i, and cannot cause the error to happen. However, without the exact queries you are running, and the exact schema of your database, we cannot be sure that we are doing exactly what you are doing. If you will call Pervasive support and tell them that you would like the latest FTF "field test file" for the ODBC engine they can send it to you. FTFs are not full releases and they are not tested at the same level of a full release from Pervasive, but they can provide you with a hot fix for something which you need to do your development. Refer to Issue 32083. If this does not resolve your issue, then repost a message and we can research further.