How do I use separate columns for month, day and year and use them as one date field in SQL

I know this issue is hard to contemplate and you will ask yourself why do we need to have 3 different columns when we can have 1 column as the date but at the end you will have to ask yourself whether you need to re-engineer everything and change every bit of code OR use a workaround to get the job done and move on. It all depends on the nature of task you have been assigned and its ramifications on the whole project.

For now, lets try and see how we can use the second method to consider those 3 columns as 1 date field and use that date field in SQL to generate some reports.

mySQL has a STR_TO_DATE function, This function allows us to pass in a string and a date format and it returns it in date. So suppose we have 3 columns as follows:

  1. pday – int(3)
  2. pyear – int(4)
  3. pmonth – int(3)

The names are self explanatory i.e. one column for the day, second for the year and the third for the month. We need to make these three columns as one string and for that we need to use the CONCAT function.

Our code will become like this:

STR_TO_DATE( concat( pyear, '-', pmonth, '-', pday ) , '%Y-%m-%d' )

You can now use this code in your SQL generate reports accordingly. Take the following query for example:

select sno, make_model, yy from car where STR_TO_DATE( concat( pyear, '-', pmonth, '-', pday ) , '%Y-%m-%d' ) between '2019-01-01' and '2019-09-30'

Now even though the data is in separate columns its being treated as a date and used in SQL to achieve desired results.

Hope the above helped

Please follow and like us:
error