B4J Question Date problem in SQL DB

[strupp01]

I have a DB with multiple columns. In it a column 'Datum'.
Unfortunately I entered the wrong date format, dd.MM.yyyy.
So my data looks like this:
02.02.2017
12.12.2016
08.01.2015
etc.

I want to start with a command, e.g. delete all data from 2016 and older.

Have already tried this command:
SQL_DB.ExecNonQuery ("Delete from Rechnungen where year (Datum) <= '" 2016' ")
Unfortunately, not, year is reported as an error.

Does somebody has any idea ?

 

[OliverA]

I think you may have a misplaced double quote right after your single quote

SQL_DB.ExecNonQuery ("Delete from Rechnungen where year (Datum) <= '2016'")

 

[Knoppi]

try:

SQL_DB.ExecNonQuery ("Delete from Rechnungen where strftime('%Y',Datum) <= '2016'")

 

[strupp01]

Unfortunately does not work. Assume that the command only works with Select.
Nevertheless many thanks.

 

[OliverA]

Assume that the command only works with Select

What command? And why only in Select? You do realize that dates are one of the most non-standardized data types in SQL, with each vendor treating not only the storage differently, but even how you escape them/present them in a query. It would really help if

1) You let us know what database you are talking to
2) The exact error message (not just that you get an error message)
3) You can be a little bit more specific than - does not work

 

[strupp01]

1. As written in the first post, it is a SQL DB
2. There is no error message. Nothing happens.
3. If nothing happens, I can not report anything

 

[OliverA]

1. As written in the first post, it is a SQL DB

MS SQL, Oracle, MySQL, SQLite, FireBird, etc?

2. There is no error message. Nothing happens.

Unfortunately, not, year is reported as an error.

 

[strupp01]

SQLite-DB

 

[OliverA]

What is the output of

"SELECT YEAR(Datum) FROM Rechnungen LIMIT 10"

 

[OliverA]

Nevermind, YEAR is not supported by SQLite. It should have been

"SELECT strftime('%Y',Datum) FROM Rechnungen LIMIT 10"

My assumption here is that you get back nothing. The date must be in a particular format for SQLite to see it as a date (see https://sqlite.org/lang_datefunc.html).

 

[strupp01]

Error message :
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such function: YEAR)

 

[OliverA]

This should do it

SQL_DB.ExecNonQuery ("Delete from Rechnungen where substr(datum, length(rtrim(datum))-3) <= '2016'")

 

[OliverA]

Error message :
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such function: YEAR)

See post #10. @Knoppi was using the right function.

 

[strupp01]

Result for
"SELECT strftime('%Y',Datum) FROM Rechnungen LIMIT 10"

is null

I also believe that this is not possible. Will change the date format now or another way via select and if query with DateTime.DateParse determine and delete the lines
Thanks for your help.

 

[OliverA]

Result for
"SELECT strftime('%Y',Datum) FROM Rechnungen LIMIT 10"

is null

That was expected. As I said, the format is not what SQLite expects. If you can change the format, do so, otherwise, post #12 should do the trick (for deleting previous dates).

 

[strupp01]

The command
SQL_DB.ExecNonQuery ("Delete from Rechnungen where substr(datum, length(rtrim(datum))-3) <= '2016'")

is working.
Thank you

 

[Knoppi]

substr(datum, length(rtrim(datum))-3)

this is false!
you use german date format dd.MM.yyyy
the result of your function is MM.yyyy

note: https://sqlite.org/lang_datefunc.html
if you want to use the datetime functions of sqlite
you need this "YYYY-MM-DD HH:MM:SS" datetime format

 

[OliverA]

this is false!

No. The column Datum does not contain a valid representation of a SQLite approved date format. It's just plain dd.dd.dddd (where d is a digit). That is why strftime does not work (once more, the actual string stored in the Datum column is not in the right format for SQLite to see it as a date). Therefore my "hack" works, since all that I do is return the last four characters of the string stored in Datum.