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
ptslvAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development Software

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.