Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

WarcraftPlayer (MIS) (OP)
6 Aug 05 11:07
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 (Programmer)
7 Aug 05 9:12
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 (MIS) (OP)
8 Aug 05 9:53
Thanks for you help but i got it. Stupid mistake not checking where the ODBC was pointing to on my machine.
darrinlingle (IS/IT--Management)
19 Jan 06 11:53
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 (Programmer)
19 Jan 06 12:01
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 (IS/IT--Management)
20 Jan 06 11:19
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 (Programmer)
20 Jan 06 12:00
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 (IS/IT--Management)
23 Jan 06 12:05
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 (Programmer)
23 Jan 06 12:20
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close