Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2952
  • Last Modified:

Date query in Pervasive SQL database

I have the following query below in Microsoft SSIS when I sync data from Pervasive SQL to MS SQL

Now I want to run the same query in Pervasive SQL Control Center which does not work. I want to run the query in Pervasive to return results where I can specify either the days, months, or years

Then I also want to format the date as DD/MM/YYYY or dd-mm-yyyy. The date format in Pervasive SQL is a string as 20131129 which is Nov 29 2013

SELECT NUMBER
      ,RECNO
      ,WHSE
      ,CODE
      ,SHD_DESCRIPTION
      ,PROD_CODE      
  FROM SALES_HISTORY_DETAIL
WHERE BVRVADDDATE > (SELECT GETCCCDATE(1));
0
Gerhardpet
Asked:
Gerhardpet
1 Solution
 
Bill BachPresidentCommented:
Developers have the freedom to use whatever date format they like in applications, and the developer whose database you are accessing took such liberties, opting to use a string field instead of a date field.

If the developer included a key on the date field, then you REALLY want to use that key in a query like this, otherwise, you'll end up with a full table-scan for every query.  To do this, you MUST convert the date you want to search on to a string, and THEN use this string for the WHERE clause.

Luckily, as a string, this field collates properly, so you can use a query like this:
    SELECT * FROM Sales_History_Detail WHERE BvrvAddDate > '20140101'
to get all records from 2014.  

You can also use the LIKE parameter to dig into the string, too.  For example, to get all records from September, use:
    WHERE BvrvAddDate LIKE '____09__'
That won't use a key and will need a table-scan, but that would be expected.

To reformat the date, you'll just have to manipulate the string with the string scalar functions:
    SELECT Right(BvrvAddDate,2) + '-' + Left(Right(BvrvAddDate,4),2) + '-' + Left(BvrvAddDate,4) FROM ...
0
 
PortletPaulCommented:
>>"The date format in Pervasive SQL is a string as 20131129 "

Do you know what the actual data type for that column is?
What is GETCCCDATE(1)? (it's not a system function) What data type does it return?

>>"... which does not work"
what is the error message? else; what is the symptom of 'does not work'?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now