Android Question Having a SQLite problem with dates

P

Penfound

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

Penfound

Active Member
Licensed User
Longtime User
Changing the Datetime.DateFormat appears to make absolutely no difference to how the date is handled OR displayed.

How do you apply the format to a particular string?
 
Upvote 0

eps

Expert
Licensed User
Longtime User
DateTime format just ensures that Dates and Times within your App are handled, I would then format them for display separately so that you can manage that yourself.

OP what does your SQL code look like?

One of mine is :

B4X: 
select count(_id), startdate  from event where enddate < date('now') order by startdate

But then I use dates retrieved like this :

B4X: 
If DateTime.DateParse(Cursor.GetString("enddate")) < v_today_date_ticks Then
...
 
Last edited:
Upvote 0

eps

Expert
Licensed User
Longtime User
To format just break down the date into the component parts.. I would try this, but others may have a better idea. As I mentioned I would focus on getting the retrieval to work and then you can focus on the display issue.

DateTime.GetYear

DateTime.GetMonth

DateTime.GetDay

Then parse the Month as you see fit?
 
Upvote 0
P

Penfound

Active Member
Licensed User
Longtime User
I have a sub that generates different SQL string based on certain criteria but the sub the does the select has
B4X: 
Dim myDate AsString
DateTime.DateFormat = "yyyy-mm-dd"
myDate = DateTime.date(DateTime.Now)
this is used in the WHERE clause like so
B4X: 
WHERE ((EventMonth ='" & tempMonth & "') AND ('EventDate' >='" & myDate & "'))
The EventDate is a string which holds that date parsed fro man XML file in SQLLite db as, for example, 2014-01-23

In theory I am trying to get the SQL statement to agree that 2014-01-23 is not greater than 2014-03-21. It should evaluate to False but SQL evaluates it True.
 
Upvote 0

eps

Expert
Licensed User
Longtime User


I see the issue you need this :

B4X: 
DateTime.DateFormat= "yyyy-MM-dd"

not

DateTime.DateFormat = "yyyy-mm-dd"

mm is minutes and MM is months...

See here for info : http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html
 
Upvote 0

Reviewnow

Active Member
Licensed User
Longtime User
My Bad for the lower case MM

All you have to do is change your date format for display purpose

B4X: 
DateTime.DateFormat = "dd MMM yyyy"
    Dim tddate As String : tddate = DateTime.Date(DateTime.now)
  
    Log(tddate)
 
Upvote 0
P

Penfound

Active Member
Licensed User
Longtime User
Wow! partial success.

I can now display the dates in the correct format. Thank you for reminding me about the mm and MM

B4X: 
theMessyDate = Cursor1.GetString("EventDate") 'puts the stored date into another variable
DateTime.DateFormat = "yyyy-MM-dd"'tells the program what format the date is stored in
EventDate = DateTime.DateParse(theMessyDate) 'convertd the stored date into ticks
Dim temporaryDate AsString'belt and braces anotehr variable to stored a temp date
DateTime.DateFormat = "dd MMM yyyy"'change the date format to the one I want
temporaryDate = DateTime.Date(EventDate) 'copy the date back and change its format
EventDate=temporaryDate 'transfer the corect date back to the original variable

I think this calls for a conversion sub later one

Now just need to work on getting SQL to think the same as humans
 
Upvote 0
P

Penfound

Active Member
Licensed User
Longtime User

Thank you for that - I think I just figured it out as you typed it.
 
Upvote 0
P

Penfound

Active Member
Licensed User
Longtime User
Both problems now solved thanks to this great forum. In case someone else has the same problem...

As Erel pointed out, it is better to store the dates as ticks in SQLlite databases. This is fine is you don't need humans to view it or work with it because of the large numbers involved. However, ticks do make calculations in SQLlite work properly.

My solution was to create some global variables to hold the different formats of dates I needed
B4X: 
Sub Globals
      DimTheAmericanDate,TheEnglishDate,TheSQLDate,TheTicksDate,TheNOWDateAsString
End Sub
Take the American date received by the SQL Query
Convert it to ticks
B4X: 
TheSQLDate = Cursor1.GetString("EventDate")
DateTime.DateFormat = "yyyy-MM-dd"
TheTicksDate = DateTime.DateParse(TheSQLDate) 'convertd the stored date into ticks
SortDates

Convert the ticks date into the different formats I wanted
B4X: 
Sub SortDates()
DateTime.DateFormat = "yyyy-MM-dd"
TheSQLDate = DateTime.Date(TheTicksDate)
DateTime.DateFormat = "yyyy-MM-dd"
TheAmericanDate = DateTime.Date(TheTicksDate)
DateTime.DateFormat = "dd MMM yyyy"
TheEnglishDate = DateTime.Date(TheTicksDate)
End Sub

and everything works perfectly!

Grateful thanks to all who helped.
 
Reactions: eps
Upvote 0
You must log in or register to reply here.
Menu
Log in
Register
Cookies are required to use this site. You must accept them to continue using the site. Learn more…