5
In a comic book table I have a 'Data' field (0000-00-00) and I need to do the following: Create a field next to 'Month' that takes the date Ex: 2014-08-27 and pass the value 'August' to the month field. And so on with all the records of the Data field. How to do this?
5
The need would be to create the field, the column:
ALTER TABLE tabela ADD COLUMN mes VARCHAR(255);
And after that run the update:
SET lc_time_names = 'pt_BR';
UPDATE tabela SET mes = (SELECT MONTHNAME(data)) WHERE mes IS NULL;
This will generate the month in full in Portuguese.
Why of select in month setting ?
select selects the date in the Row it is pointed to. I thought so to do, I don’t know if there is a form without SELECT
Suffice MONTHNAME(data)
There is no inconsistency with the various Rows?
No, the update will update all null lines (where), line per line individually.
Um, I didn’t know, but it worked normal here.
OK with this quoted by Gustavo Cinque: SET lc_time_names = 'pt_BR'; UPDATE table SET Mes = (MONTHNAME(Data)) WHERE Mes IS NULL; Thank you very much
4
You can create a query that manages the updates like this
set lc_time_names = 'pt_BR';
SELECT concat('UPDATE tabela SET mes_extenso = \'', monthname(data), '\' WHERE id =', id)
FROM datas
lc_time_names, defines the locale that is responsible for translating the month name into Portuguese and monthname() returns the name of the month in full from a date:
select monthname(now())
exit:
Agosto ou august
did not know lc_time_names
I’ll run the tests and let you know if it worked.
2
Change your table in the database with alter table
ALTER TABLE `tabela`
ADD COLUMN `mes` VARCHAR(12) NULL;
Then of a update in this table, with its date:
UPDATE `tabela` SET `mes` = date_format(`data`, '%M')
This will popular the column mes with the month in full English.
A second update may be required to translate into English, something like this:
UPDATE `tabela` SET `mes` =
CASE mes
WHEN 'January' THEN 'Janeiro'
WHEN 'February' THEN 'Fevereiro'
WHEN 'March' THEN 'Março'
WHEN 'April' THEN 'Abril'
WHEN 'May' THEN 'Maio'
WHEN 'June' THEN 'Junho'
WHEN 'July' THEN 'Julho'
WHEN 'August' THEN 'Agosto'
WHEN 'September' THEN 'Setembro'
WHEN 'October' THEN 'Outubro'
WHEN 'November' THEN 'Novembro'
WHEN 'December' THEN 'Dezembro'
ELSE `mes`;
Alternatively, before the update, you can use the lc_time_names so you don’t have to translate the months' names:
SET lc_time_names = 'pt_BR';
UPDATE `tabela` SET `mes` = date_format(`data`, '%M')
I’ll run the tests and let you know if it worked.
1
Recording at base in some field:
update tabela1 set dataextenso = (CASE month(data)
when 1 then 'Janeiro'
when 2 then 'Fevereiro'
when 3 then 'Março'
when 4 then 'Abril'
when 5 then 'Maio'
when 6 then 'Junho'
when 7 then 'Julho'
when 8 then 'Agosto'
when 9 then 'Setembro'
when 10 then 'Outubro'
when 11 then 'Novembro'
when 12 then 'Dezembro'
END)
Without recording at the base:
Select id, data, mes
FROM (
SELECT
day(data) AS dia,
year(data) AS ano,
(CASE month(data)
when 1 then 'Janeiro'
when 2 then 'Fevereiro'
when 3 then 'Março'
when 4 then 'Abril'
when 5 then 'Maio'
when 6 then 'Junho'
when 7 then 'Julho'
when 8 then 'Agosto'
when 9 then 'Setembro'
when 10 then 'Outubro'
when 11 then 'Novembro'
when 12 then 'Dezembro'
END) AS mes,
id,
data
FROM tabela1
WHERE not (data is null)
) as tabela1
Another without recording at the base:
Select id, data, mes
FROM (
SELECT
day(data) AS dia,
year(data) AS ano,
(CASE monthname(data)
when 'January' then 'Janeiro'
when 'February' then 'Fevereiro'
when 'March' then 'Março'
when 'April' then 'Abril'
when 'May' then 'Maio'
when 'June' then 'Junho'
when 'July' then 'Julho'
when 'August' then 'Agosto'
when 'September' then 'Setembro'
when 'October' then 'Outubro'
when 'November' then 'Novembro'
when 'December' then 'Dezembro'
END) AS mes,
id,
data
FROM tabela1
WHERE not (data is null)
) as tabela1
I believe that Rodrigo wants to create a month column on the table, not display in a select.
@gmsantos I was still formulating the answer, but, thank you
This same gmsantos, I will test how you put down and return if something goes wrong or right.
@Rodrigosegatto is with 3 solutions, just a question to record this information, is not irrelevant since without you save you have it the same way, what the purpose, I was curious?
I am performing a Data Mining work on My TCC and need the best structured database, otherwise there are thousands of date lines one different from the other, and this way would not help in the process.
@Rodrigosegatto got it, good luck on your TCC.
0
No need to touch SQL, the secret is to keep the data pure. See the example in PHP, create only an array and manually play the months in string starting from 1.
$query = "SELECT Year(date) as ano, MONTH(date) as mes, DAY(date) as dia, guitarwars.* FROM guitarwars WHERE approved = 1 ORDER BY score DESC, date ASC";
then create an array $mes:
$mes = array(1 => "Janeiro", "Fevereiro", "Março", "Abril", "Maio", "Junho", "Julho", "Agosto", "Setembro", "Outubro", "Novembro", "Dezembro");
then you play the month value within the array call:
echo 'Mes ' . $mes[$row['mes']];
You don’t have to hit your head on anything, and that’s until good practice.
Browser other questions tagged mysql date
You are not signed in. Login or sign up in order to post.
Thank you very much to the staff who posted. I got in the first attempt using the topic quoted by Gustavo Cinque:
SET lc_time_names = 'pt_BR';
UPDATE tabela SET Mes = (MONTHNAME(Data)) WHERE Mes IS NULL;Thank you very much– Rodrigo Segatto