convert dateTime to Date

I have Query of queries which has date field in there. And date is formated as dateTime.

Then i am using jquery but i need to convert date field to date format either on QfQ level
or in javascript
LVL 19
erikTsomikSystem Architect, CF programmer Asked:
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.

nap0leonCommented:
In Javascript - this will return
  function mysqlTimeStampToDate(timestamp) {
    //function parses mysql datetime string and returns javascript Date object
    //input has to be in this format: 2007-06-05 15:26:02
    var regex=/^([0-9]{2,4})-([0-1][0-9])-([0-3][0-9]) (?:([0-2][0-9]):([0-5][0-9]):([0-5][0-9]))?$/;
    var parts=timestamp.replace(regex,"$1 $2 $3 $4 $5 $6").split(' ');
    return new Date(parts[0],parts[1]-1,parts[2],parts[3],parts[4],parts[5]);
  }

Open in new window


If you want to remove the HHMMSS, (to make it look like midnight the morning of the date in question, remove ",parts[3],parts[4],parts[5]" from the last line)
0
erikTsomikSystem Architect, CF programmer Author Commented:
this does not seems to work, The date I am getting from the query is {ts '2010-01-02 00:00:00'}
0
LowfatspreadCommented:
what is the back end database you are connecting to?

sql server 2008+, and other databases...

cast(columnname as Date) as newcolumnname

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

erikTsomikSystem Architect, CF programmer Author Commented:
i am getting an error message
Type Date is not a defined system type.
0
_agx_Commented:
How are you returning the QoQ result to jquery? Post that code.
0
LowfatspreadCommented:
what is the database system you are trying to connect to?
0
erikTsomikSystem Architect, CF programmer Author Commented:
the result returned through .getJSON
 $.getJSON(urlpage, function(data){
  for (var i = 0; i < data.recordcount; i++) {
option+=data.data.due;
}
})
0
erikTsomikSystem Architect, CF programmer Author Commented:
sql 2005 but I am on the query of queries
0
_agx_Commented:
What code is in the CF page/function? Returning the result as json (ie returnformat=json or serializeJSON function) should convert datetime values into a nicely formatted string.


0
LowfatspreadCommented:
then please remove your question from the sql query topic areas....
0
erikTsomikSystem Architect, CF programmer Author Commented:
in CF page it returns json format
0
_agx_Commented:
Then it should already be returning the date object as a nice formatted string.

Post an example of what it's returning now and what you want to return instead?
0
erikTsomikSystem Architect, CF programmer Author Commented:
it returns {ts '2012-11-22 00:00:00'}
0
_agx_Commented:
Returning json outputs a nicely formatted date for me (CF9).  Post your CF code, because it must doing something different.
0
erikTsomikSystem Architect, CF programmer Author Commented:
it is a lot of  code to be posted. So the main function return the date is the proper format. When I call this function and use it as a query of queries then it format it back. However, strangely enough I have other dates and those dates formatted just fine
0
_agx_Commented:
Not the whole thing. Just where you're returning the result as json.

> the main function return the date is the proper format. When I call this function and
> use it as a query of queries then it format it back.

So your main function runs and returns a database query? The problem only happens when you run a QoQ?
Maybe the QoQ is doing some weird conversions in the background.

What's the data type being returned from your main database query?
<cfset queryResult = runYourMainFunction()>
<cfdump var="#queryResult.NameOfTheDateColumn[1].getClass().getName()#">
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
erikTsomikSystem Architect, CF programmer Author Commented:
My main function does return date in the proper format .
0
_agx_Commented:
What about the other questions?
0
erikTsomikSystem Architect, CF programmer Author Commented:
well it constructed dynamically so I guess the date is probably formatted as string with use of DateFormat
0
_agx_Commented:
> so I guess the date is probably formatted as string with use of DateFormat

With very little to go on, I'd say you need to do the same thing with the results returned from your QoQ (ie format the values as strings).  
0
erikTsomikSystem Architect, CF programmer Author Commented:
can tell me how to format in jquery
0
Gurpreet Singh RandhawaCEOCommented:
if you are using QoQ, use the CAST function to change the value to varchar and display as date, if i understood ur question properly
0
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
JavaScript

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.