File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes SQL Date Wrap around question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL Date Wrap around question" Watch "SQL Date Wrap around question" New topic
Author

SQL Date Wrap around question

Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
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

Joined: May 26, 2003
Posts: 30749
    
156

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jacky Luk
Ranch Hand

Joined: Aug 02, 2012
Posts: 601
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

Joined: Aug 22, 2010
Posts: 3610
    
  60

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.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: SQL Date Wrap around question