Dengar's Blog Logo

Dengar's blog...
We will see what I post, probably will be some random tutorials

Tuesday, 10 May 2011

Some simple MySQL date-time functions

I often need the first of the month, the last of the month or how many days are in the month for reporting and forecasting purposes.

You can, of course, like myself in the past look up how these things are done every time you need them. But this gets frustrating and it's often easier to surround yourself with your set of own SQL-functions that give you what you need and are easy to remember.

For this purpose I give you:

  1. First of the month date:
  2. drop function if exists fn_getFirstOfMonth;
    create function fn_getFirstOfMonth(date_ datetime) returns datetime
    begin
    set @dater = date_;
    set @dater = DATE_FORMAT(@dater ,'%Y-%m-01');
    return @dater;
    end
  3. Last of the month date:
    drop function if exists fn_getLastOfMonth;
    create function fn_getLastOfMonth(date_ datetime) returns datetime
    begin
    set @dater = date_;
    set @dater = DATE_FORMAT(last_day(@dater) ,'%Y-%m-%d');
    return @dater;
    end
  4. Days in the month:
    drop function if exists fn_getDaysInMonth;
    create function fn_getDaysInMonth(date_ datetime) returns int
    begin
    set @dater = date_;
    set @dater = day(last_day(@dater));
    return @dater;
    end

Do let me know if there are other MySQL date and time functions that you would like to know.
Happy to post them here...

No comments: