Date conversion in prevasive SQL

[MyWurld]

Some genius decided that it would be a good idea to make all date fields in the table I am using look like this 20010206. Now I need to convert this into a valid date format(i.e. 02-06-2001). So I can put it in a new table that has dates being stored as datetime data types. How can I convert the original date format to the new date format.

 

[mirtheil]

If you are using ODBC and Pervasive.SQL 2000, you'll probably need to use Convert and SubString functions. For example:
"substring(convert(curdate(),sql_char),1,4) + substring(convert(curdate(),sql_char),6,2) + substring(convert(curdate(),sql_char),9,2)"
will convert curdate (today's date) into the format yyyymmdd.
Taking that a step further, you could probably do something like:
convert(substring(field,1,4) + '-' + substring(field,6,2) + '-' + substring(field,9,2), SQL_DATE)
It may not be pretty but it should work.

If you are using Pervasive.SQL 7.0 and ODBC, you can use CAST. I don't have my 7.0 docs here so I can't give the syntax.

If you are using Btrieve API (any version), you'll need to parse the string and format it according to the date data type you use (Btrieve Date, TimeStamp, etc).
Mirtheil
mirtheil@mirtheil.com
Custom VB and Btrieve development.

 

[dgillz]

dp you actually need to convert the data to a date, or just display it as a date?

many pervasive/btrieve databases - macola and accpac come to mind immediately - that store their dates as an 8 digit integer. There is a numbertodate() function that you can use to display the numbers as true dates in tools like R&R and Crystal reports. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office