Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • 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!

*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.

Students Click Here

Convert Date

Convert Date

Convert Date

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

RE: Convert Date

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'

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

RE: Convert Date

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

RE: Convert Date

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.

"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.

RE: Convert Date

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).

Certified Pervasive Developer
Certified Pervasive Technician

RE: Convert Date

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?

RE: Convert Date

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.  

Certified Pervasive Developer
Certified Pervasive Technician

RE: Convert Date

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?

RE: Convert Date

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.

Certified Pervasive Developer
Certified Pervasive Technician

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

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! Already a Member? Login

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