Help with correct SQL syntax using CONVERT? 1

[Apollo6]

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:

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.

 

[mirtheil]

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:

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]

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.

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]

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:

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]

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:

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]

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]

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]

I'm guessing on your Decimal field but this should work:

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]

Thanks for the help...

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

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

No problem. Thanks for the star..


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com