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]

Actually what you want is :

DateTime.DateFormat= "yyyy-MM-dd"

OP, have you done this? Then your calculations should work. As long as by calculations you mean the SQL retrieve you are executing.

 

[Penfound]

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?

 

[eps]

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 :

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

But then I use dates retrieved like this :

If DateTime.DateParse(Cursor.GetString("enddate")) < v_today_date_ticks Then
...

 

[eps]

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?

 

[Penfound]

I have a sub that generates different SQL string based on certain criteria but the sub the does the select has

Dim myDate AsString
DateTime.DateFormat = "yyyy-mm-dd"
myDate = DateTime.date(DateTime.Now)

this is used in the WHERE clause like so

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.

 

[eps]

I have a sub that generates different SQL string based on certain criteria but the sub the does the select has

Dim myDate AsString
DateTime.DateFormat = "yyyy-mm-dd"
myDate = DateTime.date(DateTime.Now)

this is used in the WHERE clause like so

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.

I see the issue you need this :

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

 

[Reviewnow]

My Bad for the lower case MM

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

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

 

[Penfound]

Wow! partial success.

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

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

 

[Penfound]

My Bad for the lower case MM

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

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

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

 

[Penfound]

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

Sub Globals
      DimTheAmericanDate,TheEnglishDate,TheSQLDate,TheTicksDate,TheNOWDateAsString
End Sub

Take the American date received by the SQL Query
Convert it to ticks

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

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.

 

[eps]

Good work!