24

I get the error from this line

SELECT table.field
FROM table
WHERE table.month = 'october'
AND DATEDIFF(day, table.start_date, table.end_date) < 30

The dates in my column are in the format m-d-yy

Do I need to convert this to a different format? If so how?

Using MariaDB

Alex Borsody
  • 1,908
  • 9
  • 40
  • 74
  • Is this SQL-Server? And what is the rest of your query? And what data type do these columns have? – juergen d Apr 23 '14 at 16:47
  • 2
    Please show the full query, the full error message, and state which RDBMS you are using *(MS SQL Server, MySQL, PostGreSQL, etc)*. – MatBailie Apr 23 '14 at 16:47
  • I need to convert dates in the format d-m-yy to a unix date format. I think that would need regular expressions to place a 0 in front of a number if the month or day only has one digit. – Alex Borsody Apr 23 '14 at 16:49

3 Answers3

33

According to the documentation for MariaDB DATEDIFF only takes two arguments:

Syntax

DATEDIFF(expr1,expr2)

Description

DATEDIFF() returns (expr1 – expr2) expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
9

@alex_b it is a very common observation to get confused between the syntaxes of the DATEDIFF & TIMESTAMPDIFF functions. Following link will certainly help with the syntax of DATEDIFF & this page will list all the others date related functions available in MariaDB's parent MySQL.\

Referencing the links above below is a summary -

TIMEDIFF(expr1,expr2)
expr1 - '2000:01:01 00:00:00'
expr2 - '2000:01:01 00:00:00.000001'

TIMEDIFF() returns expr1 − expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

DATEDIFF(expr1,expr2)
expr1 - '2007-12-31 23:59:59'
expr2 - '2007-12-30'

DATEDIFF() returns expr1 − expr2 expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

Below is the scenario I used it for -

  • using CURDATE for current date as 'argument1'
  • using existing varchar column as 'argument2'
  • using SET command to update a column

SET output_date = DATEDIFF(CURDATE(),input_date), ... above worked for me. Good luck!

nitinr708
  • 1,393
  • 2
  • 19
  • 29
0

DATEDIFF of datediff returns a value which is the difference between two dates in terms of days. So datediff(mydate1, mydate2) will return X number of days; where mydate1 should be greater than mydate2. I believe the SQL query below will help you achieve your desired result.

SELECT table.field
FROM table
WHERE (table.month = 'october'
AND (datediff(table.end_date, table.start_date) < 30))
mickySimons
  • 31
  • 1