Convert Date

[WarcraftPlayer]

Im trying to pull data from a pervaive table via a DTS in SQL Server. The query below returns data in the pervasive control center but when I try to run the DTS it does not return anything. I think it is because it reads the date as a string not as a date. (I had that problem in a page I was working on) I was wondering if there is a Cast or Convert function I can use. I tried using cast like you see below but it did not work.

SELECT Com_Pat_Id, Com_Line, Com_Usr_Id_Added, Com_Date_Added
FROM table1
WHERE Com_Date_Added >CAST( '2005-07-24' AS datetime) AND Com_Pra_id = 'ADS'


Thanks in advance

 

[mirtheil]

What version of Pervasive are you using? Also, did CAST give you an error?
CONVERT might be an option. It works like:
CONVERT(<somevalue>,<ODBC DataType>)
So in your case, it would be something like:
WHERE Com_Date_Added > CONVERT('2005-07-24', SQL_DATE) AND Com_Pra_id = 'ADS'


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
Custom VB and Btrieve development.

http://www.mirtheil.com

 

[WarcraftPlayer]

Thanks for you help but i got it. Stupid mistake not checking where the ODBC was pointing to on my machine.

 

[darrinlingle]

i'm using pervasive sql 2000i and have been working for hours on this small code. i'm trying to show a table's date of injury (dateofinj) 7 digit number field as a recognizable text/character date.

doing
"select dateofinj from cw02"
gives me numbers like this : 2451893

i've tried:

select dateofinj from cw02
WHERE dateofinj >CAST( '2005-07-24' AS datetime)

and get this error:
ODBC Error: SQLSTATE = 37000, Native error code = 0
Syntax Error: SELECT dateofinj FROM "CW02"
WHERE dateofinj >CAST( '2005-07-24' AS<< ??? >> datetime)

and this:

SELECT dateofinj FROM "CW02"
WHERE dateofinj > CONVERT('2005-07-24', SQL_DATE)

and get this error:
ODBC Error: SQLSTATE = S1000, Native error code = 0
Incompatible types in predicate.

i don't know the data type of this field. i think it said type 16 in the X$Field table.
i'm very new to SQL but am an expert in Delphi, Progress(4GL), and visual basic.

please help.

 

[mirtheil]

Darrinlingle,
You really should start a new thread.
CAST doesn't work with 2000i. You will need to use CONVERT. I doubt the data type is 16. Data type 16 is a BIT so it couldn't store the information you are looking for.
Is the application that generated this data made with Magic?
To make sure, what's the value returned for the following query:
select "xe$datatype" from "x$field" where "xe$name" = 'dateofinj'

The return value you are seeing (2451893) looks like the date might be stored as an integer using some algorithm (when the application stores it, not the database). If that's the case, you'll need to bring it as an integer and the convert it to a date using code (not SQL).


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[darrinlingle]

oh. i didn't know CAST didn't work in my version.
i tried the code to show the value of dateofinj and it's type 1. i don't know where i saw 16.
is type 1 an integer?
the program that created this is CompWatch. i cannot find any help on their site.
it's frustrating because showing a field as a date should be relatively easy and quick. i've spent hours on it and have tried many of the CONVERT with SQL_... paramters. none work.
now that we know it's type 1, do you know how i'd show it as a readable date?

 

[mirtheil]

Data Type 1 is an integer.
You are going to need to know how the CompWatch converts a date to the integer. You can ask them or you can try to figure it out yourslef by looking at known dates and their values. Once you've got that algorithm, you'll select the date through SQL as an integer then inside your program, convert it back to a Date using the algorithm.
It is easy to show a Date field as a date if the application stores date information as a Date.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[darrinlingle]

ok. i found the two numbers: dateofinj is 2/4/2005 and the integer is 2453406. it's not obvious what formula is used just by looking at the two. any ideas?

 

[mirtheil]

I'm not aware of anything.
You'd probably do best to contact the vendor/developer of the original application to determine the formula used.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com