Print Date from table

[sandyas]

Hi,
I have a table in Pervasive SQL. I am accessing it from ASP and getting certain fileds. I want the date field to be printed in MM/DD/YYYY format, whereas in the database it is stored as YYYYMMDD. How do I get around it.


set conn = server.createobject("adodb.connection")
set rs = Server.CreateObject("ADODB.recordset")
ConnectionString = " DRIVER={Pervasive ODBC Client Interface};ServerName=ntserver;DBQ=Quantum;DefaultDir=;UID=;PWD=;"
conn.open ConnectionString

rs.open "select OePo_Item.ITEM_ID,OePo_Item.DESC_1,convert(left(OePo_Header.PO_DATE,4)" & '-'& "right(left(OePo_Header.PO_DATE,6),2)" & '-' & "right (OePo_Header.PO_DATE,2),pdate) from OePo_Item,OePo_Header order by OePo_Item.REQUIRED_DATE,OePo_Item.ACKNOWLEDGE_DATE",conn
%>


<% while not rs.EOF %>
<tr>
<% For each item in rs.Fields %>
<td nowrap style="font-family:verdana; font-size:8px"><%Response.Write (item.value) %></td>
<% next
rs.MoveNext%>
</tr>
<% wend
rs.close
set rs = nothing

Conn.close
Set conn = nothing
%>

I tried both convert and cast functions and it gives an error. The error it gives is

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]The second parameter pdate for CONVERT is invalid.

I am using Pervasive Sql version 8. Any help would be appreciated.

Thanks

 

[mirtheil]

The format for CONVERT is:
CONVERT (expression,data type)
"pdate" is not a data type. You might try SQL_CHAR or SQL_DATE.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[sandyas]

I got it right and is working. Thanks for the help

 

[sandyas]

Hi,
How do I concatenate two colums from a table with a '/'.

select column1,column2 from table1

The column1 and column2 have to be printed as one column with a '/' in between. How do I do it with concat function or is there any other method.

Thanks

 

[mirtheil]

select column1 + '/' + column2 from table1
should work.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[sandyas]

I get an error "Incompatible types in expression"


rs.open "select OePo_Item.ITEM_ID,OePo_Item.DESC_1,convert(left(OePo_Header.PO_DATE,4)" & '-'& "right(left(OePo_Header.PO_DATE,6),2)" & '-' & "right (OePo_Header.PO_DATE,2),SQL_CHAR),UNITORDER_QTY+'/'+UNITCODE_QTY from OePo_Item,OePo_Header order by OePo_Item.REQUIRED_DATE,OePo_Item.ACKNOWLEDGE_DATE",conn
%>

 

[mirtheil]

What's the data type of UNITCODE_QTY?


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[sandyas]

I think one is a numeric and the other is character for it prints as PK/100. The tables are third party and they are read-only.

 

[mirtheil]

If they aren't the same and aren't both a "character" type, you'll get that error. You'll need to convert the numeric to a character type (using CONVERT).

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[sandyas]

I tried CONVERT(UNIORDER_QTY,CHAR(15)) and it gives a syntax error. Hope the syntax of Convert is right.

 

[mirtheil]

THe CONVERT function uses the ODBC data types. For example, SQL_CHAR or SQL_DATE.
In this case, you need SQL_CHAR.


Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[sandyas]

Thanks for helping, as this is my first program with ASP and Pervasive SQL