Android Question Having a SQLite problem with dates

Penfound · Mar 20, 2014

I am trying to SELECT records from my database WHERE the EventDate is equal to or greater than the current date. According to the SQL date functions date('NOW') should give me the current date and date(EventDate) should accept the string formatted dated to work with - but it doesn't work.

Can someone tell me what I have got wrong in this SQL WHERE clause please?

B4X:

WHERE ((EventMonth ='" & tempMonth & "') AND (date(EventDate) >= date('NOW')))

Cheers
Penfound

eps · Mar 20, 2014

Have you tried now in lowercase?

I do : where enddate < date('now')

Admittedly not the same, but you can change the < to >=

Penfound · Mar 20, 2014

eps said:
Have you tried now in lowercase?

I do : where enddate < date('now')

Admittedly not the same, but you can change the < to >=

No change for me - sorry
My dates are stored as string data i.e "23 Jan 2014" if that helps

Erel · Mar 20, 2014

If you can I recommend you to store the ticks value instead of a string.

Penfound · Mar 20, 2014

Erel said:
If you can I recommend you to store the ticks value instead of a string.

As INT or TEXT?

Mahares · Mar 20, 2014

if you use ticks, you can store it as a data type: INTEGER or TEXT in SQLite. However, my recommendation when dealing with dates in SQLite is to always store the date in this format: yyyymmdd or perhaps yyyy-mm-dd. This way, you can do all kind of date manipulation like between >=, <=, etc. You can also easily sort by date. The number is smaller that ticks, so it is easily recognizable as a date.

eps · Mar 20, 2014

Penfound said:
No change for me - sorry
My dates are stored as string data i.e "23 Jan 2014" if that helps

lowercase now?

what error did you get?

eps · Mar 20, 2014

Admittedly my dates are 2014-01-23 in sqlite

Reviewnow · Mar 20, 2014

Try this

B4X:

Dim myDate as String
DateTime.DateFormat = "yyyy-MM-dd"
myDate = DateTime.date(DateTime.Now)
Log(myDate)

WHERE ((EventMonth ='" & tempMonth & "') AND (EventDate >='" & mydate & "'"))

Penfound · Mar 20, 2014

eps said:
lowercase now?

what error did you get?

No error - it simply did not return any events at all.

eps · Mar 20, 2014

Penfound said:
No error - it simply did not return any events at all.

Okay, so if you execute the Query in SQLite browser or Firefox Add On, then play around with the Query to see what does work.. Then you should be able to break it down and get it working, then import into B4A.

Penfound · Mar 20, 2014

Reviewnow said:

Try this

B4X:

Dim myDate as String
DateTime.DateFormat = "yyyy-MM-dd"
myDate = DateTime.date(DateTime.Now)
Log(myDate)

WHERE ((EventMonth ='" & tempMonth & "') AND (EventDate >='" & mydate & "'"))

eps said:
Okay, so if you execute the Query in SQLite browser or Firefox Add On, then play around with the Query to see what does work.. Then you should be able to break it down and get it working, then import into B4A.

Think I'll have to go and get FireFox At the moment I have taken your bit of code and added another clunky bit using StringFunctions to get the three parts of the date from the string and then rejoined together to make the correct format string date. This give me the error of being an invalid double, but I think this is because something is reading an incorrect value from the db. It shows the date as being "--" but there are not blank dates.
Hey-Ho!

Reviewnow · Mar 20, 2014

Use this to actually view what your data looks like in the database then you can try and see why you are getting format errors as I am not able to view your data
http://www.b4x.com/android/forum/threads/android-sqlite-viewer.9197/

Penfound · Mar 21, 2014

Thanks to you all for your suggestion regarding browsing the Actual SQL data. I did that and found that the sequence of SQL initialise and Creating Tables and then Parsing the XML file was incorrect. I now have all my dates stored in the .db file as yyyy-mm-dd.

I now have two problems

1. The SQL query is returning all the events but not filtering out those who date is older than today's date.

B4X:

WHERE (((EventMonth ='" & tempMonth & "') AND ('EventDate' >='" & myDate & "'))

2. When writing out the date into an html file datetime.dateformat does not work to display the date as dd MMM yyyy.

B4X:

& Cursor1.GetString("EventDate")

This piece of coding seems to present an incredible amount of irritating problems for something that is so common as working with dates. I can see why Erel suggested using ticks, it is a shame we humans can't do the instant maths to read dates that way as well.

All (polite) suggestions welcome.

Penfound

tufanv · Mar 21, 2014

not sure maybe something like this may work :
mydate.DateFormat="d/M/yyyy"

eps · Mar 21, 2014

Actually what you want is :

B4X:

DateTime.DateFormat= "yyyy-MM-dd"

Penfound · Mar 21, 2014

The format that I have the dates stored in is "yyyy-mm-dd" i.e. 2014-01-23

The format I need to display them in is "dd MMM yyyy" i.e 23 Jan 2014

eps · Mar 21, 2014

You're confusing data retrieval with data display.

You can retrieve the data using the date format indicated and your SQL will work.

Then you need to reformat the date and display it, or hold it in the DB in another format and retrieve this using the query.

Penfound · Mar 21, 2014

I'm not sure that I follow what you are saying eps. At them moment I save, store and retrieve the dates as ymd but I need to display them as DMY and nothing in the way of formatting appears to make any difference.

I used to store them as DMY and they displayed perfectly but I couldn't do any calculations with them. Now I can neither calculate nor display correctly.

eps · Mar 21, 2014

Focus on one.

Get the calculations to work correctly, then the formatting should be relatively easy.

Android Question Having a SQLite problem with dates

Active Member

Expert

Active Member

B4X founder

Active Member

Expert

Expert

Expert

Active Member

Active Member

Expert

Active Member

Active Member

Active Member

Expert

Expert

Active Member

Expert

Active Member

Expert

Privacy & Transparency

Privacy & Transparency