[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 155
  • Last Modified:

Getting Truncated DateTime in query results

I am running the following query but am getting only the first 8 characters in the date results.  
I have tried "Select ID,[DATESTAMP] as DateFormat(DTGSubmit, 'mm/dd/yyyy hh:mm:ss')....." but get the following error:  
     "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

<cfset DBPath="C:\InetPub\wwwRoot\MyWeb\dbDownload\#NewFileName#">

<cfquery name="getDFI"   datasource="#DBPath#"   dbtype="OLEDB"   provider="Microsoft.Jet.OLEDB.4.0"
  providerdsn="#DBPath#"   username="Admin"   password="">
 
Select ID, DATESTAMP,Test,[Date] as PDate       FROM t_CPoF_Interview
</cfquery>

ptslv
0
ptslv
Asked:
ptslv
  • 5
  • 5
1 Solution
 
SquareHeadCommented:
Maybe:

Select ID,[DATESTAMP] = DateFormat(DTGSubmit, 'mm/dd/yyyy hh:mm:ss')....."

Note the equals sign replacing the AS keyword...
0
 
ptslvAuthor Commented:
SquareHead -

I get an 'undefined function DateFormat in query' error.
0
 
SquareHeadCommented:
might be formatdate...
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
ptslvAuthor Commented:
I've never had this problem before.  Do you think it might be because I am dynamically creating the database connection?  If I go thru an already set datasource, I don't have to do anything special to pull the dates.
0
 
SquareHeadCommented:
Well, I'm not familiar with ColdFusion, but what you are doing is mixing the CF FormatDate function in with your query.

What does the SQL query typically need to look like after the CF FormatDate function is called? Or in other words, what does the query need to look like in order to return the record(s) you need?
0
 
ptslvAuthor Commented:
The actual query is:
<cfquery name="getDFI"   datasource="#DBPath#"   dbtype="OLEDB"   provider="Microsoft.Jet.OLEDB.4.0"
  providerdsn="#DBPath#"   username="Admin"   password="">
           Select ID, DATESTAMP,Test,[Date] as PDate      FROM t_CPoF_Interview
</cfquery>
0
 
SquareHeadCommented:
I would suggest a cross post in the ColdFusion Topic Area. I can do this for you if you are limited in available points.
0
 
ptslvAuthor Commented:
I already have a cross post for the Date problem using OLEDB.  I can get the query to work using dynamic OLEDB datasource with the exception of the date problem.  However, I cannot get it to work with the dynamic ODBC datasource.
0
 
ptslvAuthor Commented:
ALL -

I found the answer to my problem in the macromedia CF forums.  
I fixed the problem by creating a blank database, creating a datasource using the empty database and adding "IN '#DBPath#' " to my query.  I did not have to do anything to the dates.

My query ended up looking like this:

<cfquery name="getDFI" datasource="dynamicDb">
      Select ID,DATESTAMP,Test,PIN, Ans_Date,[1] as Q1,[2] as Q2,[2_c] as Q2_c,[3] as Q3,[3_c] as Q3_c,[4] as Q4,[4_c] as Q4_c,[5] as Q5,[5_c] as Q5_c,[6] as Q6,[7] as Q7,[8] as Q8,[Date] as PDate
       FROM t_Interview
       IN '#DBPath#'
</cfquery>

Thanks for all the suggestions, tho!

ptslv
0
 
SquareHeadCommented:
Great, glad you figured it out
0
 
moduloCommented:
Closed, 125 points refunded.

modulo
Community Support Moderator
Experts Exchange
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now