Android Question Having a SQLite problem with dates

[Penfound]

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?

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

Cheers
Penfound

 

[eps]

Have you tried now in lowercase?

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

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

 

[Penfound]

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]

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

 

[Penfound]

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

As INT or TEXT?

 

[Mahares]

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]

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]

Admittedly my dates are 2014-01-23 in sqlite

 

[Reviewnow]

Try this

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

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

 

[Penfound]

lowercase now?

what error did you get?

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

 

[eps]

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]

Try this

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

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

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]

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]

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.

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.

& 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]

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

 

[eps]

Actually what you want is :

DateTime.DateFormat= "yyyy-MM-dd"

 

[Penfound]

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]

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]

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]

Focus on one.

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