3

I'm trying to get an SQL query to select all records from last month, I have this which from looking numerous places is exactly what I should need, and should work:

SELECT *
FROM orders
WHERE DATEPART(yy,DateOrdered) = DATEPART(yy,DATEADD(m,-1,GETDATE()))
AND DATEPART(m,DateOrdered) = DATEPART(m,DATEADD(m,-1,GETDATE()))

However I keep getting the error:

#1305 - FUNCTION retail.DATEPART does not exist

The query I'm using is word for word from other answers on here, yet I'm getting this error.

Thank you for any help -Tom

Vereonix
  • 1,341
  • 5
  • 27
  • 54

3 Answers3

7

DATEPART is a Transact-SQL function, usable with Microsoft SQL Server. From the question tags, I assume you are using MySQL as your Database Management System.

Take a look at MySQL DATEDIFF

supertopi
  • 3,469
  • 26
  • 38
3

That would not work in mysql. To translate that to mysql you could do:

SELECT *
FROM orders
WHERE YEAR(DateOrdered) = YEAR(DATE_SUB(CURDATE(), INTERVAL -1 MONTH))
AND MONTH(DateOrdered) = MONTH(DATE_SUB(CURDATE(), INTERVAL -1 MONTH))

See here for the date functions available in mysql.

Filipe Silva
  • 21,189
  • 5
  • 53
  • 68
2

It will also work. 

SELECT * 
FROM orders 
WHERE  MONTH(DateOrdered) = MONTH(CURRENT_DATE() - INTERVAL 1 MONTH) 
AND YEAR(DateOrdered) = YEAR(CURRENT_DATE() - INTERVAL 1 MONTH)
Prasad Khode
  • 6,602
  • 11
  • 44
  • 59
Mohit Sahu
  • 177
  • 2
  • 17