Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Date Wrap around question

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is the preferred way of comparing dates in JDBC + SQLite



OR



I find they are doing pretty much the same thing
But they also share a common problem - when you don't specify the year, the predicate would be wrong
But in my application, there may be times when year will not be given.
Like The database has a birthday which contains just month and day
What is your opinion?
Thanks
Jack


 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34671
367
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I would actually say "neither." If you don't have a year, you don't have a date. A better database column type would be a String of the form "MMdd" or two columns of integers.
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeanne Boyarsky wrote:I would actually say "neither." If you don't have a year, you don't have a date. A better database column type would be a String of the form "MMdd" or two columns of integers.


Wouldn't be nice to add the year to the string of his/her birthday and
compare it to the current date, so that the year number is included.
But how do I do that in SQLite?

Update:
Won't work because if today was 2013-12-31, the his bd is 01-13, his bd will return 2013-01-13, so not the upcoming year

Edit:
Will this work?


Thanks
Jack
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What are you trying to achieve? Do you have a table containing the birthdays and are you trying to list all birthdays that occur in certain interval?

Also, it doesn't make sense asking us whether your code will work. You can easily test that yourself, can't you? If you want to ask whether a specific approach will work, it would be better if you described that approach in English instead of asking us to deduce it from your code.

In any case, I'd prefer to perform the comparison on the dates, not their textual representations. For example, I'd get the day and month, plugged in the current year, and compared that date to the interval of my interest. If the interval overlaps the next year too, I'd try the comparison with the next year too. But this is just my preference. It is certainly possible to implement the logic using the texts, not the dates, I just consider the version with the DATE datatype more readable.

Another possibility, assuming the interval of interest is always short, would be to create the MMDD representation of every day in the interval and use just an IN operator to select matching records.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic