B4J Question Date problem in SQL DB

S

strupp01

Active Member
Licensed User
Longtime User
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 ?
 
Sort by date Sort by votes
OliverA

OliverA

Expert
Licensed User
Longtime User
I think you may have a misplaced double quote right after your single quote
B4X: 
SQL_DB.ExecNonQuery ("Delete from Rechnungen where year (Datum) <= '2016'")
 
Upvote 0
Knoppi

Knoppi

Active Member
Licensed User
Longtime User
try:
B4X: 
SQL_DB.ExecNonQuery ("Delete from Rechnungen where strftime('%Y',Datum) <= '2016'")
 
Upvote 0
S

strupp01

Active Member
Licensed User
Longtime User
Unfortunately does not work. Assume that the command only works with Select.
Nevertheless many thanks.
 
Upvote 0
OliverA

OliverA

Expert
Licensed User
Longtime User
strupp01 said:
Assume that the command only works with Select
Click to expand...
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
 
Upvote 0
S

strupp01

Active Member
Licensed User
Longtime User
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
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Upvote 0

OliverA

Expert
Licensed User
Longtime User
What is the output of
B4X: 
"SELECT YEAR(Datum) FROM Rechnungen LIMIT 10"
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Nevermind, YEAR is not supported by SQLite. It should have been
B4X: 
"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).
 
Upvote 0
S

strupp01

Active Member
Licensed User
Longtime User
Error message :
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such function: YEAR)
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
This should do it
B4X: 
SQL_DB.ExecNonQuery ("Delete from Rechnungen where substr(datum, length(rtrim(datum))-3) <= '2016'")
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
strupp01 said:
Error message :
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (no such function: YEAR)
Click to expand...
See post #10. @Knoppi was using the right function.
 
Upvote 0
S

strupp01

Active Member
Licensed User
Longtime User
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.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
strupp01 said:
Result for
"SELECT strftime('%Y',Datum) FROM Rechnungen LIMIT 10"

is null
Click to expand...
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).
 
Upvote 0
S

strupp01

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

is working.
Thank you
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Knoppi said:
this is false!
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar Threads

  • Question
B4J Question [B4X] B4XTable and date fields
Replies
17
Views
2K
LucaMs
J
  • Question
B4J Question Problem POI with dates from excel
Replies
3
Views
776
emexes
E
  • Article
B4J Code Snippet MySQL Negative Dates - Long DateTime (such as Birthdays) problem
Replies
0
Views
2K
Harris
  • Question
B4J Question jtelegrambot lib "date" problem
Replies
7
Views
1K
behnam_tr
A
  • Question
B4J Question MySQL Date conversion
Replies
4
Views
2K
Adrian Trantino
A