0

I have two dates date column like '2017-12-29' and '2018-01-05' the datediff between these dates is 8, but i want to know the datediff which fall on 2018 which should be 5. I'm working on mysql.

  • 1
    Did you mean '2017-12-29' instead of '2017-31-29'? – Rogerlr May 4 '18 at 12:53
  • So the first date to actually compare is the 1st of January of the first year, that is past the given first date. Is that right? – sticky bit May 4 '18 at 20:12
  • @Rogerlr i corrected my question. – Arpan May 5 '18 at 5:35
0

If I'm understanding correct, you need only the days from the year of your second date. Based in the answer from dbdemon, you could use a CASE:

CREATE TABLE t1 (
  id int unsigned primary key, 
  date1 date, 
  date2 date
);

INSERT INTO t1 (id, date1, date2) 
  VALUES (1, '2017-12-29', '2018-01-05'),
  (2, '2018-02-20', '2018-03-05');

SELECT 
  date1,
  date2,
  DATEDIFF(date2,CASE YEAR(date1) WHEN YEAR(date2) THEN date1 ELSE MAKEDATE(YEAR(date2),1) END) 
FROM t1;

That way, if the dates are from the same year, the difference will be the same, if there from different year it'll count only starting from first day of the year.

0

Does this work?

CREATE TABLE t1 (
  id int unsigned primary key, 
  date1 date, 
  date2 date
);

INTSERT INTO t1 (id, date1, date2) 
  VALUES (1, '2017-12-29', '2018-01-05');

SELECT id, 
  datediff(date2, date1) "days since date1", 
  datediff(date2, makedate(year(date2),1)) "days since new year" 
FROM t1;
+----+------------------+---------------------+
| id | days since date1 | days since new year |
+----+------------------+---------------------+
|  1 |                7 |                   4 |
+----+------------------+---------------------+

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.