Sql query delete dates

1

Hi,

I have an sql table and a number of columns.

One of the columns stores dates in the format yyyy-mm-dd. I need to delete records that are older than 10 days.
I’ve tried numerous ways but can’t get it to work using a query.
Assume the ‘?’ is a date in format yyyy-mm-dd below and ‘dateF’ is the column holding the dates.

sqlList = [] 
sqlList[0] = ["DELETE FROM Stats WHERE CAST(dateF as datetime) < ?;", args, DeleteS, DeleteF]
Sql(DB1, sqlList)

Any help is greatly appreciated.
Thanks.

2

The only problem I see is that you’re comparing a CAST( … as datetime) to a string which is in a date format, not a datetime one…

3

I tried changing to a date format instead of datetime, and all records are deleted regardless of value.

4

What is in args variable?
Have you tried a preconfigured SQL string such as
DELETE FROM Stats WHERE CAST(dateF as datetime) < “2018-01-01”
?
Have you tried a different approach, such as using DATEDIFF(CAST(dateF AS date), NOW())>10 ?

5

I haven’t looked at it for quite awhile, but I have a REPLACE query in an app that compares dates in YYYY-MM-DD format which executes fine. Here’s the syntax I’m using (BASIC) modified for your string example:

sqlList[0] = ["DELETE FROM Stats WHERE Date(dateF) < Date(?);", args, DeleteS, DeleteF]

Kind regards,
Doug

6

Thanks for your suggestions.

Doug’s suggestion was the only one i could get working:

sqlList[0] = [“DELETE FROM Stats WHERE Date(dateF) < Date(?);”, args, DeleteS, DeleteF]

Thank you