×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

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

Print Date from table

Print Date from table

Print Date from table

(OP)
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

RE: Print Date from table

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

RE: Print Date from table

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

RE: Print Date from table

(OP)
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

RE: Print Date from table

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

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

RE: Print Date from table

(OP)
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
%>    

RE: Print Date from table

What's the data type of UNITCODE_QTY?

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

RE: Print Date from table

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

RE: Print Date from table

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

RE: Print Date from table

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

RE: Print Date from table

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

RE: Print Date from table

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

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