CODEWITHSUNDEEP
sqloracle-databasecoldfusiontimestampto-char
Emanshaun
Emanshaun

Reputation: 37

Convert oracle sql timestamp to char coldfusion

What I am trying to do is converting the oracle timestamp to_char so it is readable by the human eye.

The following is my SQL code: 

<cfquery datasource="#application.dsn#" name="hdlbillDetails">
    SELECT BILLID, FIRSTNAME, 
           LASTNAME, RESIDENCE, 
           STREET, TOWN, 
           COUNTRY, AMMOUNTDUE, 
           to_char(DATEDUE,'YYYY-MM-DD HH24:MI:SS'), 
           to_char(DATEISSUES,'YYYY-MM-DD HH24:MI:SS')
    FROM Details
    WHERE BillID = #billId#
</cfquery>

The Error shown is the following:

Caused by: coldfusion.runtime.UndefinedElementException: 
Element DATEDUE is undefined in HDLBILLDETAIL

Upvotes: 1

Views: 3231

Answers (2)

jdev
jdev

Reputation: 5622

try using alias e.g 

<cfquery datasource="#application.dsn#" name="hdlbillDetails">
    SELECT d.BILLID, d.FIRSTNAME, 
           d.LASTNAME, d.RESIDENCE, 
           d.STREET, d.TOWN, 
           d.COUNTRY, d.AMMOUNTDUE, 
           to_char(d.DATEDUE,'YYYY-MM-DD HH24:MI:SS') as DATEDUE, 
           to_char(d.DATEISSUES,'YYYY-MM-DD HH24:MI:SS') as DATEISSUES
    FROM Details d
    WHERE d.BillID = #billId#
</cfquery>

Upvotes: 2

Gerry Gurevich
Gerry Gurevich

Reputation: 936

Untested, but try this. It might be just a need for aliases on the calculations:

<cfquery datasource="#application.dsn#" name="hdlbillDetails">
    SELECT BILLID, FIRSTNAME, 
           LASTNAME, RESIDENCE, 
           STREET, TOWN, 
           COUNTRY, AMMOUNTDUE, 
           to_char(DATEDUE,'YYYY-MM-DD HH24:MI:SS') as DATEDUE, 
           to_char(DATEISSUES,'YYYY-MM-DD HH24:MI:SS') as DATEISSUES
    FROM Details
    WHERE BillID = #billId#
</cfquery>

Upvotes: 8

Related Questions