CODEWITHSUNDEEP
mysqlsql
user1088537
user1088537

Reputation: 179

Setting date format within a SQL query

I am fetching data from 3 different tables (using INNER JOIN) to create an HTML code that gives me a random portrait photo of a person and the persons birth- and deathdates (see the result here: https://www.hafdal.dk/legstadaleit/ - below the headstone image).

This is my query:

SELECT CONCAT (
    '<center><img src="https://www.hafdal.dk/tng/andlitsmyndir/',
    tng_media.path,'" alt="',
    tng_media.description,
    '" width="250"><br><a href="https://www.hafdal.dk/legstadaleit/index.php/database/?page=www.hafdal.dk/tng/getperson.php%3FpersonID%3D',
    tng_medialinks.personID,
    '"><b>',
    tng_media.description, 
    '</b><br>', 
    'f. ', 
    tng_people.birthdate, 
    ' - d. ', 
    tng_people.deathdate, 
    '</a></center>') 
FROM `tng_media`   
INNER JOIN tng_medialinks on tng_media.mediaID=tng_medialinks.mediaID  
INNER JOIN tng_people on tng_people.personID=tng_medialinks.personID   
WHERE mediatypeID="11"
ORDER BY RAND() 
LIMIT 1

How can I format the date so that it looks Icelandic, i.e. the month names are in Icelandic and there is a . after the date number and month name? Like this: 12. des. 2018.

I have tried inserting: 

DATE_FORMAT (tng_people.birthdate, '%d. %M %Y')

where tng_people.birthdate is, but that does not work, unfortunately.

Is is possible to change the date format, with the SQL query? I have no possibilities of adding/changing the database, just pulling data from it.

The format of birthdate in the database is "dd MMM YYYYY" in English (varchar(50)). I also have the possibility of using the variable birthdatetr which is "YYYY-MM-DD" (date).

I cannot change the overall language of the database because I also use it in Danish and English. So I need to figure out how to do this

enter image description here

Upvotes: 0

Views: 662

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

From Date_Format() documentation:

The language used for day and month names and abbreviations is controlled by the value of the lc_time_names system variable

For Iceland, the locale value is is_IS. Check full list at: https://dev.mysql.com/doc/refman/5.7/en/locale-support.html

You will need to set the system variable before calling the query:

SET SESSION lc_time_names = 'is_IS';

Also, your Date_format() specifier string needs to be changed to: 

DATE_FORMAT (tng_people.birthdate, '%d. %b. %Y')

Details:

  • %d Day of the month, numeric (00..31)
  • %b Abbreviated month name (Jan..Dec)
  • %Y Year, numeric, four digits

Upvotes: 1

Related Questions