CODEWITHSUNDEEP
sql-servercoldfusion
Vicki
Vicki

Reputation: 1456

CF/SQL converting datetime to MM/DD/YYYY

I am using ColdFusion and SQL Server. I am trying to convert the Due_Date into MM/DD/YYYY.

The database is set up like this and unfortunately I am not able to make any alterations to it.enter image description here.

I am making a call like so: 

<cffunction name="displayTable" access="public" returntype="query">
    <cfquery name="processTable">
        SELECT id, Date_Due, Date_Complete, Item_Count
        FROM dbo.Dealer_Track_Work      
    </cfquery>
    <cfreturn processTable>
</cffunction>

I have tried many combinations of something similar to this:

<cffunction name="displayTable" access="public" returntype="query">
    <cfquery name="processTable">
        SELECT id, LEFT(CONVERT(VARCHAR, Date_Due, 120), Date_Complete, Item_Count
        FROM dbo.Dealer_Track_Work      
    </cfquery>
    <cfreturn processTable>
</cffunction>

Can anyone spot where I may be going wrong? I was thinking because the database is set up as datetime and this is converting to varchar but I still cannot come up with the correct combination. (And am not allowed to change the table) Any assistance in getting my output to read MM/DD/YYYY would be greatly appreciated.

Upvotes: 1

Views: 337

Answers (2)

garateca
garateca

Reputation: 51

Should not you be using 101?

<cffunction name="displayTable" access="public" returntype="query">
  <cfquery name="processTable">
    SELECT id, CONVERT(CHAR(10), Date_Due, 101), Date_Complete, Item_Count
    FROM dbo.Dealer_Track_Work      
  </cfquery>
  <cfreturn processTable>
</cffunction>

Upvotes: 3

Matt Busche
Matt Busche

Reputation: 14333

I would run your query like below. Don't forget to var scope your processTable variable

<cffunction name="displayTable" access="public" returntype="query">
  <cfset var processTable = ''>
  <cfquery name="processTable">
    SELECT id, Date_Due, Date_Complete, Item_Count
    FROM dbo.Dealer_Track_Work      
  </cfquery>
  <cfreturn processTable>
</cffunction>

and then perform the format on the ColdFusion side

#dateFormat(processTable.Date_Due, 'mm/dd/yyyy')#

Upvotes: 8

Related Questions