Since your subdateshow is a VARCHAR column instead of the proper [DATE, TIMESTAMP or DATETIME column][1] you have to convert the string to date before you can use YEAR on it:

    SELECT YEAR(STR_TO_DATE(subdateshow, &quot;%m/%d/%Y&quot;)) from table

See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date


  [1]: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-types.html

    SELECT EXTRACT(YEAR FROM subdateshow) FROM tbl_name;

You can try this:

    SELECT EXTRACT(YEAR FROM field) FROM table WHERE id=1

This should work:

    SELECT YEAR(STR_TO_DATE(subdateshow, &#39;%m/%d/%Y&#39;)) FROM table;

eg:

    SELECT YEAR(STR_TO_DATE(&#39;01/17/2009&#39;, &#39;%m/%d/%Y&#39;));  /* shows &quot;2009&quot; */

try this code:

    SELECT DATE_FORMAT(FROM_UNIXTIME(field), &#39;%Y&#39;) FROM table

This should work if the date format is consistent:

    select SUBSTRING_INDEX( subdateshow,&quot;/&quot;,-1 ) from table 

TRY:

    SELECT EXTRACT(YEAR FROM (STR_TO_DATE(subdateshow, &#39;%d/%m/%Y&#39;)));


try this code:

`SELECT YEAR( str_to_date( subdateshow, &#39;%m/%d/%Y&#39; ) ) AS Mydate`

For some odd reason, I can&#39;t use breakpoints in my eclipse project. All breakpoints that I set have a diagonal line on them, above the dot in the respective line (on the left margin of the window), as if they are blocked. When I run in debug mode, the breakpoints are ignored. Any ideas?


why can&#39;t I set up a breakpoint in eclipse?

so I&#39;ve been toying with this calendar-ish thingy for a bit:

- Grid of divs (mimicking a table)
- Hovering over a table cell displays a tooltip with 2 icons each consisting of a div with :before and :after elements
- Icons change colour depending on colour of cell hovered and that of its previous sibling (cell&#39;s colour class is applied to the icon).

Stripped down fiddle: http://jsfiddle.net/e9PkA/1/

This works fine in every browser but IE8 and below (IE lte 7 and I will never friends, but IE8 would be nice to have).

IE8 notices the change of classNames and updates the divs&#39; colour accordingly but completely ignores the colour changes implied by the :before and :after declarations, e.g.:


    .wbscal_icon_arrival:before {
        width: 12px;
        height: 4px;
        
        left: -8px;
        top: 6px;
        background-color: silver;
    }
    
    .wbscal_icon_arrival.wbscal_full:before {
        background-color: #ff0000 !important; 
    }

In the fiddle above, the :before/:after elements are coloured exactly once: the first time the tooltip is shown. 

In another version it would update everytime I&#39;d move the mouse out of the &quot;table&quot; div, but not if the tooltip is hidden when hovering a &quot;cell&quot; div border.

I&#39;ve tried force-triggering repaints by adding/removing other classes to/from the element/its parents/the body, editing/accessing style attributes and whatnot so I guess it&#39;s not your average repaint problem.

Is there a JS hack that fixes this and forces :before/:after to update?

Forcing IE8 to rerender/repaint :before/:after pseudo elements

I need a mysql query to extract the year from the following date format from a table in my database. 

For eg : 
  

            subdateshow
          ----------------		
            01/17/2009
            01/17/2009
            01/17/2009
            01/17/2009
            01/17/2009

the following query didn&#39;t working 

    select YEAR ( subdateshow ) from table 

The column type is varchar. Is there any way to solve this?
    	

mysql extract year from date format

I need a mysql query to extract the year from the following date format from a table in my database.
For eg :
<pre><code class="hljs language-lua"> subdateshow
 ----------------		
 01/17/2009
 01/17/2009
 01/17/2009
 01/17/2009
 01/17/2009
</code></pre>
the following query didn't working
<pre><code class="hljs language-sql">select YEAR ( subdateshow ) from table 
</code></pre>
The column type is varchar. Is there any way to solve this?

I am building a query for a report with multiple IF conditions on the SUM. I am having problems with a multiple IF conditions on the SUM.

Here is the query:

    SELECT SUM(`totalamount`) AS Total, 
	SUM(`PayPalFee`) AS Fees,
	DATE(`TransactionDate`) AS `Day`, 
	SUM(IF(PaymentType = &quot;paypal&quot;, 1,0)) AS Paypal, 
	SUM(IF(PaymentType = &quot;check&quot;, 1,0)) AS Checks, 
	SUM(IF(PaymentType = &quot;credit card&quot;, 1,0)) AS CreditCard, 
	COUNT(*) AS Entries
     FROM my_table
     WHERE TransactionDate between &#39;2011-05-05&#39; AND &#39;2012-01-30&#39;
     GROUP BY day
     ORDER BY `day` ASC

This query works just fine. 

When I try to add the below conditional SUM statement:

     SUM(&#39;TotalAmount&#39;(PaymentType = &quot;credit card&quot;, 1,0)) AS CreditCardTotal,

This conditional IF statement fails out. 

I have a column called &#39;TotalAmount&#39; and a column called &#39;PaymentType&#39; I am looking to create a SUM of the credit card transactions by each day, a SUM of the checks transactions by each day, a SUM of the paypal transactions by each day,. 
I have tried to create a subquery but this returns a value for the entire TotalAmount column, not broken down by day.

MYSQL Sum Query with IF Condition

When try to connect to the mysql server through my rails application, I get the following error

    D:/Program_Files/Ruby192/lib/ruby/site_ruby/1.9.1/rubygems/custom_require.rb:36:in `require&#39;: 
    Incorrect MySQL client library version! This gem was compiled for 6.0.0 but the client library is 5.0.27. (RuntimeError)

How can I rectify it? 

mysql2 gem compiled for wrong mysql client library

I know questions with this title have been answered before, but please do read on. I&#39;ve read thoroughly all the other questions/answers on this error before posting.

I am getting the above error for the following query:

    CREATE TABLE IF NOT EXISTS `pds_core_menu_items` (
      `menu_id` varchar(32) NOT NULL,
      `parent_menu_id` int(32) unsigned DEFAULT NULL,
      `menu_name` varchar(255) DEFAULT NULL,
      `menu_link` varchar(255) DEFAULT NULL,
      `plugin` varchar(255) DEFAULT NULL,
      `menu_type` int(1) DEFAULT NULL,
      `extend` varchar(255) DEFAULT NULL,
      `new_window` int(1) DEFAULT NULL,
      `rank` int(100) DEFAULT NULL,
      `hide` int(1) DEFAULT NULL,
      `template_id` int(32) unsigned DEFAULT NULL,
      `alias` varchar(255) DEFAULT NULL,
      `layout` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`menu_id`),
      KEY `index` (`parent_menu_id`,`menu_link`,`plugin`,`alias`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Does anyone have idea why and how to fix it?
The catch is - this same query works perfectly on my local machine, and worked as well on my previous host. Btw.it&#39;s from a mature project - phpdevshell - so I&#39;d guess these guys know what they are doing, although you never know.

Any clue appreciated.

I&#39;m using phpMyAdmin.

#1071 - Specified key was too long; max key length is 1000 bytes

What is the default order of a query when no `ORDER BY` is used?

SQL: What is the default Order By of queries?

How to do that?

Former title of this question was &quot;*using rank (@Rank := @Rank + 1) in complex query with subqueries - will it work?*&quot; because I was looking for solution using ranks, but now I see that the solution posted by Bill is much much better.

Original question:

I&#39;m trying to compose a query that would take last record from each group given some defined order:

    SET @Rank=0;

    select s.*
    from (select GroupId, max(Rank) AS MaxRank
          from (select GroupId, @Rank := @Rank + 1 AS Rank 
                from Table
                order by OrderField
                ) as t
          group by GroupId) as t 
      join (
          select *, @Rank := @Rank + 1 AS Rank
          from Table
          order by OrderField
          ) as s 
      on t.GroupId = s.GroupId and t.MaxRank = s.Rank
    order by OrderField

Expression `@Rank := @Rank + 1` is normally used for rank, but for me it looks suspicious when used in 2 subqueries, but initialized only once. Will it work this way?

And second, will it work with one subquery that is evaluated multiple times? Like subquery in where (or having) clause (another way how to write the above):

    SET @Rank=0;

    select Table.*, @Rank := @Rank + 1 AS Rank
    from Table
    having Rank = (select max(Rank) AS MaxRank
                  from (select GroupId, @Rank := @Rank + 1 AS Rank 
                        from Table as t0
                        order by OrderField
                        ) as t
                  where t.GroupId = table.GroupId
                 )
    order by OrderField

Thanks in advance!

Content Type	Original Author	Original Content on Stackoverflow
Question	Renjith R	View Question on Stackoverflow
Solution 1 - Mysql	Gordon	View Answer on Stackoverflow
Solution 2 - Mysql	NooNa MarJa	View Answer on Stackoverflow
Solution 3 - Mysql	Jamshid Hashimi	View Answer on Stackoverflow
Solution 4 - Mysql	pgl	View Answer on Stackoverflow
Solution 5 - Mysql	ram4nd	View Answer on Stackoverflow
Solution 6 - Mysql	emcomments	View Answer on Stackoverflow
Solution 7 - Mysql	Sudhir Bastakoti	View Answer on Stackoverflow
Solution 8 - Mysql	Boopathi	View Answer on Stackoverflow

mysql extract year from date format

Mysql Problem Overview

Mysql Solutions

Solution 1 - Mysql

Solution 2 - Mysql

Solution 3 - Mysql

Solution 4 - Mysql

Solution 5 - Mysql

Solution 6 - Mysql

Solution 7 - Mysql

Solution 8 - Mysql

Forcing IE8 to rerender/repaint :before/:after pseudo elements

why can't I set up a breakpoint in eclipse?

Attributions