0

I have 2 date columns that are displayed side-by-side. I have some dates that are equal. I do not want to display the "equal" dates/duplicate date values in one of the columns.

Example:

    column1     column2
    1-1-2014    1-1-2014

I want to display:

    column1     column2
    1-1-2014          

How do I achieve that?

3

See the DDL, DML and SQL below, it should get you some (all?) of the way. It's taken from MySQL, but should be fairly generic.

Take a look at this (MS SQL Server) and also this (more general). Check the docco of your particular server for any or all which may apply.

CREATE TABLE fred 
(
  billy int(11) DEFAULT NULL,
  d1 date DEFAULT NULL,
  d2 date DEFAULT NULL
);

mysql> desc fred;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| billy | int(11) | YES  |     | NULL    |       |
| d1    | date    | YES  |     | NULL    |       |
| d2    | date    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into fred values(1, '20140606', '20140607');
Query OK, 1 row affected (0.09 sec)

mysql> insert into fred values(1, '20140606', '20140606');  <<<=== SAME
Query OK, 1 row affected (0.07 sec)

mysql> insert into fred values(1, '20140607', '20140608');
Query OK, 1 row affected (0.06 sec)

mysql> select * from fred;
+-------+------------+------------+
| billy | d1         | d2         |
+-------+------------+------------+
|     1 | 2014-06-06 | 2014-06-07 |
|     1 | 2014-06-06 | 2014-06-06 |  <<=== SAME
|     1 | 2014-06-07 | 2014-06-08 |
+-------+------------+------------+
3 rows in set (0.00 sec)

SELECT billy, d1, 
  CASE
    WHEN d1 = d2 
      THEN NULL
    ELSE 
      d2
  END AS d2 
FROM fred;

+-------+------------+------------+
| billy | d1         | d2         |
+-------+------------+------------+
|     1 | 2014-06-06 | 2014-06-07 |
|     1 | 2014-06-06 | NULL       | <<== ***
|     1 | 2014-06-07 | 2014-06-08 |
+-------+------------+------------+ 
1

Considering your table's name is "date_table", then you can select like this

SELECT date_table1.`column1`, date_table2.`column2`
FROM date_table AS date_table1
LEFT JOIN date_table AS date_table2 
                     ON date_table1.`column1` = date_table2.`column1` 
                     AND date_table1.`column2` = date_table2.`column2` 
                     AND date_table1.`column1` != date_table2.`column2`
1

T-SQL (SQL Server) has a built-in function for this purpose, NULLIF().

SELECT leftcol AS a, NULLIF(rightcol, leftcol) AS b
FROM myTable;

If rightcol equals leftcol, the NULLIF() expression in column b will return a NULL value. I'm sure there are similar functions in other SQL dialects as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.