How to extract year from date datatype
Moderator: General Moderators
How to extract year from date datatype
Hi there, good day. Does anyone know how to extract year on a date datatype in mysql?
Like for instance, i want to get the all the 2008 records from my db. I know that mysql date saves a record in this format YYYY-MM-DD. All i need is the year. Any help?
Thank you.
Like for instance, i want to get the all the 2008 records from my db. I know that mysql date saves a record in this format YYYY-MM-DD. All i need is the year. Any help?
Thank you.
Re: How to extract year from date datatype
I think, we have discussed it earlier.
Did you try this:
Did you try this:
Code: Select all
SELECT SUBSTR(FIELDNAME,0,4) FROM TABLENAME;
Re: How to extract year from date datatype
yes i have tried that pcoder, still nothing. It doesn't work out,
i have tried
It works. But when the table contains any 2008 value, it adds that value to $expense_total
i have tried
Code: Select all
$year = "2008";
$queryExpense = "SELECT * FROM tbl_inventory WHERE date LIKE('$year%')";
$expenseResult = mysql_query($queryExpense);
//SELECT DAY(your_date_field) AS dtDay, MONTH(your_date_field) AS dtMonth, YEAR(your_date_field) AS dtYear FROM your_table
while($row1 = mysql_fetch_assoc($expenseResult)){
$expense = $row1['amt'];
$expense_total = $expense_total + $expense;
}
$dexpense_total = number_format($expense_total, 2, '.', '');
$expense_display = "Total Expense: <b><font color = \"#CC0000\">P ".$dexpense_total."</font></b>";
echo $expense_display;Re: How to extract year from date datatype
Replace your $queryExpense with:
I think it works. 
Code: Select all
$queryExpense = "SELECT * FROM tbl_inventory WHERE SUBSTR(date,0,4) = $year.
Re: How to extract year from date datatype
No luck, it doens't work
Re: How to extract year from date datatype
I don't know, why this function didn't work. If you have format like 'YYYY-DD-DD', then it should have to work.
Anyway, i just got this junction. Try this:
EXTRACT
Anyway, i just got this junction. Try this:
EXTRACT
Re: How to extract year from date datatype
How will do it if i use EXTRACT sir? What will be the code?
Re: How to extract year from date datatype
You might find having a column called 'date' is breaking your queries. Date is a reserved word because it's a MySQL function name. Ideally you should rename that column, or at least you use backticks whenever you access it.
Also, pcoder is giving you some pretty awful advice. Never treat a date column as if it's a string. That will be dreadfully slow on a large set of rows. Use the proper date functions. In this case that would be YEAR().
Also, pcoder is giving you some pretty awful advice. Never treat a date column as if it's a string. That will be dreadfully slow on a large set of rows. Use the proper date functions. In this case that would be YEAR().
Code: Select all
SELECT *
FROM `tbl_inventory`
WHERE 1
AND YEAR(`date`) = $yearRe: How to extract year from date datatype
Thanks sir onion2k. Still no luck sir, yes i have a column name date which has a datatype of date also.
Please help me
Please help me
Re: How to extract year from date datatype
hey sir onion2k, thanks a lot. I was wrong. It worked out. .. . Thank you very much. . .
And to all who helped me out. Thanks a lot guys.
And to all who helped me out. Thanks a lot guys.
Re: How to extract year from date datatype
Actually, i don't have more experience on mysql database. I am working in oracle database from the beginning And i am trying to help zplits as much as i can.
Anyway thanks onion2k for your comments and sorry zplits for the wrong prescribtions.
Anyway thanks onion2k for your comments and sorry zplits for the wrong prescribtions.
Re: How to extract year from date datatype
It's okay sir pcoder.
don't worry. It's really okay. I have learned something from you. Thanks for helping me. Thanks a lot.
Re: How to extract year from date datatype
If you were doing the same thing in Oracle you'd need to use TO_CHAR(`date`, 'YYYY') rather than a SUBSTR(). MySQL's date functions (eg YEAR() ) are the same as the TO_CHAR() function in Oracle.pcoder wrote:Actually, i don't have more experience on mysql database. I am working in oracle database from the beginning
Re: How to extract year from date datatype
Yeah i know about the TO_CHAR() in oracle. And i was not getting the equivalent in MYSQL.
Thanks a lot onion2k for your suggestion.
Thanks a lot onion2k for your suggestion.