0

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 a 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 an 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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Leron
  • 9,546
  • 35
  • 156
  • 257
  • Just to make it clear for those that my try this with CodeIgniter and AR. To make this work you need to make second select just for this. Here is what I mean : `$this->db->select("GROUP_CONCAT(DATE_FORMAT( mails_dates.dt_sending_date, '%d-%m-%Y')) AS sending_dates", FALSE );` – Leron Jun 12 '12 at 09:57

2 Answers2

3

Try this..

SELECT GROUP_CONCAT(DATE_FORMAT( date_time_column, '%d-%m-%Y' )) FROM test_table;
manurajhada
  • 5,284
  • 3
  • 24
  • 43
  • Looks like should do the trick but I get this error: `Error Number: 1054, Unknown column '%d-%m-%Y' in 'field list'`. Maybe it's in my syntax, I use CodeIgniter AR and the selections looks like this:`GROUP_CONCAT(DATE_FORMAT( mails_dates.dt_sending_date, %d-%m-%Y )) AS sending_dates` – Leron Jun 12 '12 at 07:49
  • you are using Mysql or something else? The syntax I have written is checked on mysql.. you can format it acc to you if you are using something else any api or tool. – manurajhada Jun 12 '12 at 07:56
  • phpmyadmin and CodeIgniter AR – Leron Jun 12 '12 at 07:59
  • No idea about CodeIgniter AR. You can run the query at phpmyadmin, if you are not getting desired result, please let me know. – manurajhada Jun 12 '12 at 08:09
  • 1
    yes I do, gonna accept your answer, I'm just waiting to see if anyone with CodeIgniter experience won't writer something, because obv. the problem is there. – Leron Jun 12 '12 at 08:11
1

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.

Community
  • 1
  • 1
Bjoern
  • 15,934
  • 4
  • 43
  • 48
  • Thanks gonna look though all your links, but I think for this case it wont be problem to use `DATE_FORMAT`, could you see my comment on the first answer and point where could the problem be in my `SELECT` statement for getting this error? – Leron Jun 12 '12 at 07:55
  • Escape the date format in `DATE_FORMAT` properly. Instead of `DATE_FORMAT( mails_dates.dt_sending_date, %d-%m-%Y )` use `DATE_FORMAT( mails_dates.dt_sending_date, '%d-%m-%Y' )`! – Bjoern Jun 12 '12 at 08:25
  • sadly, but that was my first thought too - don't work. Anyways, thanks a lot, there is a lot of useful info in your answer. Big thanks. – Leron Jun 12 '12 at 08:54