convert dateTime to Date

erikTsomik
erikTsomik used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
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)
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'}
Top Expert 2011

Commented:
what is the back end database you are connecting to?

sql server 2008+, and other databases...

cast(columnname as Date) as newcolumnname

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

erikTsomikSystem Architect, CF programmer

Author

Commented:
i am getting an error message
Type Date is not a defined system type.
Most Valuable Expert 2015

Commented:
How are you returning the QoQ result to jquery? Post that code.
Top Expert 2011

Commented:
what is the database system you are trying to connect to?
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;
}
})
erikTsomikSystem Architect, CF programmer

Author

Commented:
sql 2005 but I am on the query of queries
Most Valuable Expert 2015

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.


Top Expert 2011

Commented:
then please remove your question from the sql query topic areas....
erikTsomikSystem Architect, CF programmer

Author

Commented:
in CF page it returns json format
Most Valuable Expert 2015

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?
erikTsomikSystem Architect, CF programmer

Author

Commented:
it returns {ts '2012-11-22 00:00:00'}
Most Valuable Expert 2015

Commented:
Returning json outputs a nicely formatted date for me (CF9).  Post your CF code, because it must doing something different.
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
Most Valuable Expert 2015
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()#">
erikTsomikSystem Architect, CF programmer

Author

Commented:
My main function does return date in the proper format .
Most Valuable Expert 2015

Commented:
What about the other questions?
erikTsomikSystem Architect, CF programmer

Author

Commented:
well it constructed dynamically so I guess the date is probably formatted as string with use of DateFormat
Most Valuable Expert 2015

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).  
erikTsomikSystem Architect, CF programmer

Author

Commented:
can tell me how to format in jquery
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial