I’m learning how to work with dates stored in database tables, and it’s cool that there are so many things you can do with them, but the sheer number of options makes it really confusing. I’m also running into all kinds of quirks; the latest is the error message “out of range value” for years like 1600.
So I’m thinking of a new scheme. First, my page URL’s look like this: MySite/Calendar/January_1, or MySite/Calendar/July_4
So rather than mess with numerical date/datetime fields, I want to create a simple char field (URL) with the appropriate values (e.g. July_4). Then, to avoid problems with years (like “out of range value”), I want to change my year field to an integer field. It would look like this:
Date (char) | Year (int)
July_4 | 1966
July_4 | 1970
July_23 | 1432
But I want to be able to order by date, also - like this:
Jan. 1, 1329
Jan. 1, 1948
Jan. 1, 2010
Jan. 2, 1946
Feb. 7, 1940
Oct. 11, 1984
So I want to create a date or datetime field that I cite in an ORDER BY query clause. Which would be the best choice (date or datetime), and - if I name the field DateOrder - would I just order it by adding the clause “ORDER BY DateOrder”?
I’m assuming the final DB values would look like this:
July_4 | 1966 | 1966-07-04 (date field)
July_4 | 1966 | 1966-07-04 00-00-00 (datetime field)
And if I’m dealing with a situation where I know the year but not the date, it would look like this:
Ocober_10 | 1966 | 1966-00-00 (date field)
October_10 | 1966 | 1966-00-00 00-00-00 (datetime field)
Thanks.