Menu
I've seen a few other examples of this for SQL, but I am looking particularly for MySQL.
This is the code I have (which works, but I think it's drastically inefficient). I am using the arbitrary date '2011-05-15' which should and does return '2011-06-30'.
DATE_SUB(
DATE_ADD(
CONCAT(
YEAR( CURDATE() ),
'-01-01'
),
INTERVAL QUARTER('2011-05-15') QUARTER
),
INTERVAL 1 DAY
)
What is the better way to do this?
548
QUARTER() function MySQL QUARTER() returns the quarter of the year for a date. The return value is in the range of 1 to 4. Syntax: QUARTER(date);
The DATE_ADD() function adds a time/date interval to a date and then returns the date.
The DATE_SUB() function subtracts a time/date interval from a date and then returns the date.
Use the MONTH() function to retrieve a month from a date/datetime/timestamp column in MySQL. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a date/datetime/timestamp column.
In general, built-in functions in MySQL are very fast compared to other things like disk and memory I/O so they have little impact on efficiency.
You could probably save some milliseconds by getting rid of string conversions:
SELECT MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) QUARTER - INTERVAL 1 DAY
but, again, I wouldn't even worry for such optimizations.
156
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
