MySQL Date & Time functions

There is exclusive fields type to store Date and Date - Time in mysql or in other databases. As we know in any script date plays important role and we need to do many calculation and formatting to handle the date issues. It can be a simple Library book issue management to a customer compliant handling, these date fields are important for handling related data in a table.

MySQL have several date & time related functions to use in different situations and requirements. We will discuss some of the important functions here and use them for developing some sample codes.

You can read php date & time functions.
FunctionDescription
BETWEENRecords Between two dates
CURDATE()Current Date using MYSQL Query
DATE_ADD()Add by specifying INTERVAL in days, month, year , Hour, minutes etc
DATE_SUB()Subtracting Day, month , year , time etc
EXTRACT()Get part of date and time
STR_TO_DATE()Convert String to Date & time Format as required by MySQL date field
TO_DAYS()Difference in days between two Dates
DATE_FORMAT()Formatting Date & Time output as required
DAY()Day part of the date field
DAYNAME()Day Name in Full ( Monday )
DAYOFWEEK()Weekdays starting from Sunday as 1 and ending with Saturday as 7
DAYOFYEAR()Number of the day in a Year ( 1 to 366 )
LAST_DAY()Last Day of the month
MONTH()Month part of the date field
MAKEDATE()Date from Year and DayOfYear
MONTHNAME()Full Month name from date
NOW()Current Date and Time
QUARTER()Quarter of Year Data
YEAR()Year part of the date field
YEARWEEK()Year with Week number of the year
WEEK()Week number in a year (0 - 53)
WEEKDAY()Day Week number in a week (0 - 6)
WEEKOFYEAR()Week number in a year (1 - 53)

DEMO of Date Functions

From Date of Birth Calculate the retirement date

The person will retire on the last working day of the retirement month.

Group by in date field to count records of each day

Getting records of each day by using group by and count command in mysql date field

Data of different quarters of the year

Different Financial Quarter data

Format to use for creating date string before storing in Date field.

Date fields of MySQL table accept a particular format of Date & time strings, so we have to create such an acceptable format while storing Date values.

Collecting date stored as string in varchar field.

We can use str_to_date function to format date value stored as string in a varchar field ( not date field )

Adding present date & time automatically to mysql table

We can store the present date & time in a DateTime field while updating adding a record by using default value to CURRENT_TIMESTAMP

Formatting returned date value using strtotime function

We can format the returned date value from table by using strtotime function.

Generating query

based on user inputs of date from two calendars

Today's records



Practice Questions



Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    deepak verma

    13-10-2009

    a really good work. one of the best sites that gives u exact working answers
    noraina norddin

    11-03-2010

    how to insert current time in mysql for automatic using php??
    sayed

    24-02-2012

    really good

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer