Converting string with multiple dates into a dd-mm-YYYY format in JavaScript (PHP/SQL) ont he backend

I have a DB table with relation 1:N where N represents multiple dates for one event. The column with the dates is DateTime type, and I would like to keep the Time option for later use, but it won't be so bad if I have to change it to Date type.

The problem comes when I have to show those multiple dates in some GUI. I get the dates with the GROUP_CONCAT function which means that in JavaScript I operate with a string with comma separated values representing the different dates, which by now is in the default SQL DateTime format - YYYY-mm-dd hh:mm:ss.

I use the split(',') function to get each date-time value and what I can do is to change the type of the SQL column to Date so when I split the string in JavaScript to end up with YYYY-mm-dd values. Which should be reversed to dd-mm-YYYY for the GUI.

I'm not sure how to proceed here. I have in mind two main options:

first: Maybe there's way to use dd-mm-YYYY format in SQL which will solve all the problems.

second: some kind of (complex?!?) string manipulation in JavaScript to split the string of dates into array with multiple elements and then try to format each element the way I need.

Honestly - I want to avoid the second option, but don't know if the first is possible, and maybe, there's another way that I haven't think of. So any suggestions are appreciated.

Thanks

Leron

Answers


Try this..

SELECT GROUP_CONCAT(DATE_FORMAT( date_time_column, '%d-%m-%Y' )) FROM test_table;

First of all I advise to use the native Date/DateTime-format everywhere in your code and only use localized variations like dd-mm-yyyy only where you really want to display it. First reason: it is consistent in your code. Second reason: Sorting.

Example:

2012-03-01    >    01-03-2012
2012-05-12    >    12-05-2012
2012-01-03    >    03-01-2012 

Sorted by the native format you'll get...

1: 2012-01-03
2: 2012-03-01
3: 2012-05-12

Sorted by the output format dd-mm-yyyy it will look like this...

1: 01-03-2012
2: 03-01-2012
3: 12-05-2012

...and I doubt this is what most people want.

Adjusting the output via SQL

You can change the output in your SELECT-query , most RDBMS offer functions for this. For example, in MySQL it is DATE_FORMAT, which looks like this:

SELECT DATE_FORMAT(NOW(), '%d-%m-%Y');

Adjusting the output via SQL

You can also change the output using php, which is explained here.

Adjusting the output via JavaScript

It isn't hard to do this in JavaScript, too: Here is a great SO post which explains it in detail.


Need Your Help

Authlogic's current_user object in models

ruby-on-rails authlogic

I need to know the ID of the current user in a model:

Deserializing a JSON Array as List

c# json list deserialization

I'm trying to grab the tweets from a super huge JSON file... all i want are the ones named "text"