How to store month and year in the mm/yyyy format in SQL-CodePudding

I want to create a table that stores month and year of a bill in the "mm/yyyy" format. Is there a data type that I can use directly or I will have to think of some other way?

CodePudding user response：

Use date data type and then when you retrieve the data from the table, you can format it as mm/yyyy by using DATE_FORMAT in your select statement as follows:

DATE_FORMAT(`yourdatecolumname`, "%m-%Y") FROM `yourtablename` WHERE....

CodePudding user response：

If the size of the data is not important to you, you can use columns for month and day, or you can just keep the date and ask it in the format DATE_FORMAT(your_date,'%Y%d'). If you care about the footprint of the data, define an integer and make sense of its bits. For example 4 bits for month and 8 bits for year. for 09/2021; 9:1001 21:0001 0101 decimal 2325. month information CEIL(2325/256) and the year information is 2325%6. But as I mentioned at the beginning, if the size of the data is not important, you don't have to deal with them.