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.

Help with correct SQL syntax using CONVERT?Helpful Member! 

Apollo6 (TechnicalUser) (OP)
3 Mar 07 9:01
In my database I have a birthdate field, type decimal.  Example of a record would be 19721003, i.e. 10/03/1972.  I am using the following syntax where $whatMonth = '10'.  Ulitmately, I want to pull out all the records with a birthday in a specific month, then order them by day:

CODE

SELECT lastname, altaddr1, altaddr2, altaddr4, altcntry, CONVERT(birthdate, SQL_CHAR) FROM SOINC.UPEMPL where status <> 3 CONVERT(SUBSTRING(birthdate,5,2), SQL_INTEGER) = '$whatMonth'
This is not working and I have tried CAST as well but no luck with that.  I am sure it is just that I don't know the exact syntax.  Any help would be great.
Helpful Member!  mirtheil (Programmer)
3 Mar 07 13:48
A few questions:
1. What version of PSQL are you using?
2. What behavior are you seeing (error, incorrect results)?
3. What happens if you hard code a value instead of using $whatMonth?  

One comment: Because you're converting to an integer, the variable needs to be an integer.  
What happens if you change the query to:

CODE

SELECT lastname, altaddr1, altaddr2, altaddr4, altcntry, CONVERT(birthdate, SQL_CHAR) FROM SOINC.UPEMPL where status <> 3 CONVERT(SUBSTRING(birthdate,5,2), SQL_INTEGER) = $whatMonth

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Apollo6 (TechnicalUser) (OP)
3 Mar 07 16:35
1.  Could you help me in determining exactly which version of the database I am running on?  I can see that I am running the Control Cener version 8.6; how can I determine the exact database version I am on?
2/3.

CODE

Warning: SQL error: [Pervasive][ODBC Client Interface][Pervasive][ODBC Engine Interface]Syntax Error: SELECT lastname, altaddr1, altaddr2, altaddr4, altcntry, CONVERT(birthdate, SQL_CHAR) FROM SOINC.UPEMPL where status <> 3 CONVERT<< ??? >>(SUBSTRING(birthdate,5,2), SQL_INTEGER) = '03' UNION , SQL state 37000 in SQLExecDirect in c:\webdocs\empdr\html\display_bday.php on line 27
I am not able to get past this error regardless if I hardcode the variable.

Let me know if I can provide more information.
Thanks.
mirtheil (Programmer)
4 Mar 07 10:30
There are couple of ways to determine the version:
1. Check the version properties of W3ODBCEI.DLL and W3ODBCCI.DLL on the client and server.
2. Issue a BUTIL -VER at the command line.  It'll tell you the version.

Actually your syntax is incorrect.  Try the following:

CODE

SELECT lastname, altaddr1, altaddr2, altaddr4, altcntry, CONVERT(birthdate, SQL_CHAR) FROM SOINC.UPEMPL where status <> 3 AND CONVERT(SUBSTRING(birthdate,5,2), SQL_INTEGER) = $whatMonth

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Apollo6 (TechnicalUser) (OP)
4 Mar 07 11:43
From executing the version stuff, it states v8.60, I guess I was expecting something about the database itself, i.e. 2000i or something like that.  I read some post that said CAST would not work with this but since I'm not using that, shouldn't be the issue.

I used the syntax you stated and received the following, even if I hardcode the variable:

CODE

Warning: SQL error: [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Incompatible types in expression, SQL state S1000 in SQLExecDirect...
This is leading me to a situation where I'm either comparing a string to a number or vice versa.  I will keep playing with it.  Thanks for the help.
mirtheil (Programmer)
4 Mar 07 14:47
CAST was available in PSQL v7 and was reintroduced in PSQL 9.  PSQL 2000i was actually v7.9x (7.90 for SP3 and 7.94 for SP4).  

One thing you should try would be to issue the query in PCC and once it works there, convert it to the PHP code.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Apollo6 (TechnicalUser) (OP)
8 Mar 07 14:13
Mirtheil-

I tried attempting to execute the SQL in the PCC but still get the exact same SQL error...  I did determine by looking at the table design that the field [birthdate] is a 'decimal' type.  That being said, how can I write the SQL statement to pull out just records where birthdate = 03?  For example, if the birthdate is stored as 19450301 (decimal type)?

I would think this would be straight forward SQL but I can't get the correct syntax to get the return recordset I want.  Any additional suggestions would be appreciated!
mirtheil (Programmer)
8 Mar 07 14:27
I'm guessing on your Decimal field but this should work:

CODE

create table ddate  (ddate decimal(15,0))#
insert into ddate (ddate) values (19450301)#
select substring(convert(ddate, sql_char),5,2) from ddate

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician
http://www.mirtheil.com

Apollo6 (TechnicalUser) (OP)
8 Mar 07 15:54
Thanks for the help...

This is the final statement that I got to work...

CODE

"SELECT substring(convert(birthdate, sql_char),7,2), lastname, altaddr1, altaddr2, altaddr4, altcntry, birthdate FROM SOINC.UPEMPL where status <> 3 AND substring(convert(birthdate, sql_char),5,2) = '$whatMonth'
                UNION
                SELECT substring(convert(birthdate, sql_char),7,2), lastname, altaddr1, altaddr2, altaddr4, altcntry, birthdate FROM TOPLLC.UPEMPL where status <> 3 AND substring(convert(birthdate, sql_char),5,2) = '$whatMonth'";

As you can see, I am bouncing against two databases with the same schema structure, i.e. two different companies.  I also needed the results to print out in 'Day' order so I had to peel out the day from the birthdate field.  Thanks again for leading me to the water!!!
mirtheil (Programmer)
8 Mar 07 16:12
No problem. Thanks for the star..

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!

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