Date Formats and Functions in MySQL Database Server (Full Programming Calendar)

That's article is helpful in database developing.

Current Date & Time:

SELECT  SYSDATE( ) AS Date_Time;

Current Date:

SELECT  CURDATE( ) AS CurrentDate;

Day Name:

SELECT  DAYNAME(CURDATE( )) as DAY_NAME;

Day Of Week:

SELECT  DAYOFWEEK(CURDATE( )) as DAY_OF_WEEK;

Day Of Month:

SELECT  DAYOFMONTH(CURDATE( )) as DAY_OF_MONTH;

Day Of Quarter:

SELECT  TIMESTAMPDIFF(DAY,TIMESTAMPADD(quarter,TIMESTAMPDIFF(quarter,'1900-01-01',CURDATE()),'1900-01-01'),CURDATE()) + 1 as DAY_OF_QUARTER;

Day Of Half Year:

SELECT  case when month(CURDATE()) <= 6

then 

DAYOFYEAR(CURDATE())

else

TIMESTAMPDIFF(DAY, TIMESTAMPADD(month, 6, TIMESTAMPADD(year, TIMESTAMPDIFF(year, '1900-01-01', CURDATE()), '1900-01-01')), CURDATE())+1

         end

                                                 DAY_OF_HALFYEAR;

Day Of Year:

SELECT  DAYOFYEAR(CURDATE()) as DAY_OF_YEAR;

Fortnight:

SELECT  case when day(CURDATE()) >15 then 2 else 1 end FORTNIGHT;

Week Of Month:

SELECT  WEEK(CURDATE(),6) - WEEK(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY),6)+1 as WEEK_OF_MONTH

Week Of Quarter:

SELECT  TIMESTAMPDIFF(WEEK,TIMESTAMPADD(quarter,TIMESTAMPDIFF(quarter,'1900-01-01',CURDATE()),'1900-01-01'),CURDATE()) + 1 as WEEK_OF_QUARTER

Week Of Half Year:

SELECT  case when month(CURDATE()) <= 6 

then 

WEEKOFYEAR(CURDATE())

else

TIMESTAMPDIFF(week, TIMESTAMPADD(month, 6, TIMESTAMPADD(year, TIMESTAMPDIFF(year, '1900-01-01', CURDATE()), '1900-01-01')), CURDATE())

end WEEK_OF_HALFYEAR

Week Of Year:

SELECT   WEEKOFYEAR(CURDATE()) as WEEK_OF_YEAR;

Month Name Full:

SELECT  MONTHNAME(CURDATE()) MONTH_NAME_FULL;

Month Name Short:

SELECT  DATE_FORMAT(CURDATE(),'%b') MONTH_NAME_SHORT

Month Of Quarter:

SELECT  TIMESTAMPDIFF(MONTH,TIMESTAMPADD(quarter,TIMESTAMPDIFF(quarter,'1900-01-01',CURDATE()),'1900-01-01'),CURDATE()) + 1 as MONTH_OF_QUARTER

Month Of Half Year:

SELECT  case when month(CURDATE()) <= 6 

then 

MONTH(CURDATE())

else

TIMESTAMPDIFF(MONTH, TIMESTAMPADD(month, 6, TIMESTAMPADD(year, TIMESTAMPDIFF(year, '1900-01-01', CURDATE()), '1900-01-01')), CURDATE())

                  end 

                                          MONTH_OF_HALFYEAR

Month Of Year:

SELECT  MONTH(CURDATE()) as MONTH_OF_YEAR

Quarter Name:

SELECT  case QUARTER(CURDATE()) when 1 then 'First' when 2 then 'Second' when 3 then 'Third' else 'Fourth' end AS QUARTER_NAME

Quarter Of Half Year:

SELECT  case when month(CURDATE()) <= 6 

then 

QUARTER(CURDATE())

else

TIMESTAMPDIFF(QUARTER, TIMESTAMPADD(month, 6, TIMESTAMPADD(year, TIMESTAMPDIFF(year, '1900-01-01', CURDATE()), '1900-01-01')), CURDATE())

end 

QUARTER_OF_HALFYEAR

Quarter Of Year:

SELECT  QUARTER(CURDATE()) QUARTER_OF_YEAR

Half Of Year:

SELECT  case when MONTH(CURDATE()) > 6 then 2 else 1 end HALF_OF_YEAR

Year Full:

SELECT  YEAR(CURDATE()) as YEAR_FULL

Year Short:

SELECT  Right(Year(CURDATE()),2)  YEAR_SHORT

Month Year:

SELECT  CONCAT(DATE_FORMAT(CURDATE(),'%b'),'-',Right(Year(CURDATE()),2)) MONTH_YEAR

Is Holiday?:

SELECT  CASE DAYNAME(CURDATE()) WHEN 'SUNDAY ' THEN 'Holiday' WHEN 'SATURDAY ' THEN 'Holiday ' ELSE 'Working Day' END AS IS_HOLIDAY

Holiday Name:

SELECT  CASE DAYNAME(CURDATE()) WHEN 'SUNDAY ' THEN 'SUNDAY ' WHEN 'SATURDAY ' THEN 'SATURDAY ' ELSE 'NULL' END AS HOLIDAY_NAME

Fiscal Year:

SELECT  CASE MONTH(CURDATE())>=6 WHEN TRUE THEN CONCAT(YEAR(CURDATE()),'-',YEAR(CURDATE())+1) 

WHEN FALSE THEN CONCAT(YEAR(CURDATE())-1,'-',YEAR(CURDATE())) END AS FISCAL_YEAR

***************************************

Share With friends and add comment for more.