1

i need to perform an operation (in this case DATEDIFF()) on 2 dates. But at least one of those dates is incomplete.

For example: DATEDIF('2010-01-00','2010-02-01') OR DATEDIF('2010-01-00','2010-02-00') will return NULL

Indeed, in http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff, the manual, it says DATEDIF can't handle incomplete dates, as all the date arithmetic functions.

As i can't,don't want to change my database and i really need to do this operation, do you know a way to do it, a trick?

I ask in general even if my actual issue is to perform this DATEDIF.

dyesdyes
  • 1,147
  • 3
  • 24
  • 39
  • 2
    Why are they incomplete in the first place? What data type are they? – Pekka Nov 09 '11 at 18:29
  • They are incomplete because we don't to have a day in it. The field type is date in mysql. – dyesdyes Nov 09 '11 at 18:30
  • 1
    If you have a year and a month but no day, then what is the meaning of "difference between two days"? – Toomai Nov 09 '11 at 18:33
  • Ok, in this case, how can i change all the '2010-01-00' in '2010-01-01' ? I know it doesn't really make sense but it is stored like this in the db and i can't change it. We suppose that if there no day given, it is the first of the month. – dyesdyes Nov 09 '11 at 18:39

1 Answers1

1

You can use IF and CONCAT to check for incomplete dates and replace 00 with 01.

IF ( isnull(CONCAT(v6.title,v7.title,v8.title)), IF ( isnull(CONCAT(v7.title,v8.title)), v8.title, 
        CONCAT(v7.title,\'.\',v8.title) ),
        CONCAT(v6.title,\'.\',v7.title,\'.\',v8.title) ) AS "Datum",
        IF ( isnull(CONCAT(v9.title,v10.title)) & isnull(CONCAT(v11.title, v12.title)), v13.title, 
                   IF ( isnull(CONCAT(v9.title,v10.title)) & isnull(v13.title), CONCAT(v11.title,\'-\', v12.title),
                    IF ( isnull(CONCAT(v11.title,v12.title)) & isnull(v13.title), CONCAT(v9.title,\':\', v10.title, \' Uhr\'),
                    "Hello World" ) ) ) AS "Uhrzeit",
Micromega
  • 12,486
  • 7
  • 35
  • 72