Visual Studio Query Builder Filtering by current date minus one year?

Hi,

I'm currently building a front end database web app w/ VS2013 and MySQL 5.6.

I'm trying to set up a query that brings up all hardware purchased within certain dates where it checks the current date, subtracts a year, and pulls up all hardware purchased within that time frame.

Formerly in an old MS Access database, the query string was set up within  ((tblHardware.PurDate)<Now()-365) and it worked fine, but SQL server doesn't play nice with that.

It will almost always bring up an error regarding the requirement of a to_date function or having an sql syntax error with mysql. (using date_add and curdate)

Any suggestions?

Thanks

September 10th, 2015 3:40pm

Hi,

I'm currently building a front end database web app w/ VS2013 and MySQL 5.6.

Formerly in an old MS Access database, the query string was set up within  ((tblHardware.PurDate)<Now()-365) and it worked fine, but SQL server doesn't play nice with that.

It will almost always bring up an error regarding the requirement of a to_date function or having an sql syntax error with mysql. (using date_add and curdate)

Any suggestions?

Thanks

Hi Pika,

  • ((tblHardware.PurDate)<Now()-365)  works in SQL Server as ((tblHardware.PurDate)<GETDATE()-365)
  • to_date is an Oracle function I believe. In SQL Server use CAST('20150701' AS DATE) and in MySQL use STR_TO_DATE

As you stated your application has a MySQL end, not sure why SQL Server mentioned in your description. Since this is a forum for SQL Server, for MySQL questions, please consult in a dedicated forum. It is more appropriate and you would get more prompt and precise res

Free Windows Admin Tool Kit Click here and download it now
September 10th, 2015 11:18pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics