Menu
I am building an app that has a daily quote that should be stored in the database. Each quote is assigned with a day of the year, including one for Feb 29th. Since the quote only cares about the day not the year should I still use smalldatetime type? Please let me know your opinions, thanks!!
777
You can extract the year using the extract() function in standard SQL. The function takes date or DateTime objects and returns the year as a string.
The default way to store a date in a MySQL database is by using DATE. The proper format of a DATE is: YYYY-MM-DD. If you try to enter a date in a format other than the Year-Month-Day format, it might work but it won't be storing the dates as you expect.
If you need to retain the day and month data, you might as well use SmallDateTime and simply ignore the year component (or set it to the same value across the board, for example 2000 which was a leap year, so leap dates will be allowed).
You still get to use proper date and time functions with the correct data type and if you go with a VARCHAR field you will end up converting to and from it.
51
I can't help feeling that if the calendar had been invented by a software engineer that the leap day would be the 32nd of December rather than the 29th of Feb. That way you could simply use a smallint offset from the 1st Jan.
You can still use a smallint offset from the 1st of March, with 1st March as 0, 2nd March as 1, 29th Feb as 365. However it involves doing some kind of custom conversion to the figure you want and may not sort as you would like to.
Given that you can store the Day and Month as two tinyints taking up the same space, I'm not sure this would be a good plan, but thought I would mention it for completeness.
44
answered Oct 08 '22 00:10
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
