How to extract year from date datatype

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

How to extract year from date datatype

Post by zplits »

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.
User avatar
pcoder
Forum Contributor
Posts: 230
Joined: Fri Nov 03, 2006 5:19 am

Re: How to extract year from date datatype

Post by pcoder »

I think, we have discussed it earlier.
Did you try this:

Code: Select all

 
SELECT SUBSTR(FIELDNAME,0,4) FROM TABLENAME;
 
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: How to extract year from date datatype

Post by zplits »

yes i have tried that pcoder, still nothing. It doesn't work out,

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;
It works. But when the table contains any 2008 value, it adds that value to $expense_total
User avatar
pcoder
Forum Contributor
Posts: 230
Joined: Fri Nov 03, 2006 5:19 am

Re: How to extract year from date datatype

Post by pcoder »

Replace your $queryExpense with:

Code: Select all

 
$queryExpense = "SELECT * FROM tbl_inventory WHERE SUBSTR(date,0,4) = $year.
 
 
I think it works. 8)
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: How to extract year from date datatype

Post by zplits »

No luck, it doens't work
User avatar
pcoder
Forum Contributor
Posts: 230
Joined: Fri Nov 03, 2006 5:19 am

Re: How to extract year from date datatype

Post by pcoder »

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
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: How to extract year from date datatype

Post by zplits »

How will do it if i use EXTRACT sir? What will be the code?
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: How to extract year from date datatype

Post by onion2k »

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().

Code: Select all

SELECT * 
FROM `tbl_inventory` 
WHERE 1 
AND YEAR(`date`) = $year
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: How to extract year from date datatype

Post by zplits »

Thanks sir onion2k. Still no luck sir, yes i have a column name date which has a datatype of date also.

Please help me
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: How to extract year from date datatype

Post by zplits »

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.
User avatar
pcoder
Forum Contributor
Posts: 230
Joined: Fri Nov 03, 2006 5:19 am

Re: How to extract year from date datatype

Post by pcoder »

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. :)
User avatar
zplits
Forum Contributor
Posts: 158
Joined: Sun Aug 03, 2008 8:59 pm

Re: How to extract year from date datatype

Post by zplits »

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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: How to extract year from date datatype

Post by onion2k »

pcoder wrote:Actually, i don't have more experience on mysql database. I am working in oracle database from the beginning
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.
User avatar
pcoder
Forum Contributor
Posts: 230
Joined: Fri Nov 03, 2006 5:19 am

Re: How to extract year from date datatype

Post by pcoder »

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. :)
Post Reply