Learn to Code SQL Example – SQL | Date functions

Hits: 12

(SQL Example for Citizen Data Scientist & Business Analyst)

 

SQL | Date functions

In SQL, dates are complicated for newbies, since while working with database, the format of the date in table must be matched with the input date in order to insert. In various scenarios instead of date, datetime (time is also involved with date) is used.
In MySql the default date functions are:

  • NOW(): Returns the current date and time. Example:
    SELECT NOW();
    

    Output:

    2017-01-13 08:03:52
    
  • CURDATE(): Returns the current date. Example:
    SELECT CURDATE();
    

    Output:

    2017-01-13
    
  • CURTIME(): Returns the current time. Example:
    SELECT CURTIME();
    

    Output:

    08:05:15
    
  • DATE(): Extracts the date part of a date or date/time expression. Example:
    For the below table named ‘Test’

    Id Name BirthTime
    4120 Pratik 1996-09-26 16:44:15.581
    SELECT Name, DATE(BirthTime) AS BirthDate FROM Test;
    

    Output:

    Name BirthDate
    Pratik 1996-09-26
  • EXTRACT(): Returns a single part of a date/time. Syntax:
    EXTRACT(unit FORM date);
    

    There are several units that can be considered but only some are used such as:
    MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, etc.
    And ‘date’ is a valid date expression.

    Example:
    For the below table named ‘Test’

    Id Name BirthTime
    4120 Pratik 1996-09-26 16:44:15.581

    Queries

    • SELECT Name, Extract(DAY FROM BirthTime) AS BirthDay FROM Test;
      

      Output:

      Name BirthDay
      Pratik 26
    • SELECT Name, Extract(YEAR FROM BirthTime) AS BirthYear FROM Test;
      

      Output:

      Name BirthYear
      Pratik 1996
    • SELECT Name, Extract(SECOND FROM BirthTime) AS BirthSecond FROM Test;
      

      Output:

      Name BirthSecond
      Pratik 581
  • DATE_ADD() : Adds a specified time interval to a date
    Syntax:

    DATE_ADD(date, INTERVAL expr type);
    

    Where,  date – valid date expression and expr is the number of interval we want to add.
    and type can be one of the following:
    MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, etc.

    Example:
    For the below table named ‘Test’

    Id Name BirthTime
    4120 Pratik 1996-09-26 16:44:15.581

    Queries

    • SELECT Name, DATE_ADD(BirthTime, INTERVAL 1 YEAR) AS BirthTimeModified FROM Test;
      

      Output:

      Name BirthTimeModified
      Pratik 1997-09-26 16:44:15.581
    • SELECT Name, DATE_ADD(BirthTime, INTERVAL 30 DAY) AS BirthDayModified FROM Test;
      

      Output:

      Name BirthDayModified
      Pratik 1996-10-26 16:44:15.581
    • SELECT Name, DATE_ADD(BirthTime, INTERVAL 4 HOUR) AS BirthHourModified FROM Test;
      

      Output:

      Name BirthSecond
      Pratik 1996-10-26 20:44:15.581
  • DATE_SUB(): Subtracts a specified time interval from a date. Syntax for DATE_SUB is same as DATE_ADD just the difference is that DATE_SUB is used to subtract a given interval of date.
  • DATEDIFF(): Returns the number of days between two dates.Syntax:
    DATEDIFF(date1, date2);
    date1 & date2- date/time expression
    

    Example:

    SELECT DATE_DIFF('2017-01-13','2017-01-03') AS DateDiff;
    

    Output:

    DateDiff
    10
  • DATE_FORMAT(): Displays date/time data in different formats.Syntax:
    DATE_FORMAT(date,format);
    

    date is a valid date and format specifies the output format for the date/time. The formats that can be used are:

    • %a-Abbreviated weekday name (Sun-Sat)
    • %b-Abbreviated month name (Jan-Dec)
    • %c-Month, numeric (0-12)
    • %D-Day of month with English suffix (0th, 1st, 2nd, 3rd)
    • %d-Day of month, numeric (00-31)
    • %e-Day of month, numeric (0-31)
    • %f-Microseconds (000000-999999)
    • %H-Hour (00-23)
    • %h-Hour (01-12)
    • %I-Hour (01-12)
    • %i-Minutes, numeric (00-59)
    • %j-Day of year (001-366)
    • %k-Hour (0-23)
    • %l-Hour (1-12)
    • %M-Month name (January-December)
    • %m-Month, numeric (00-12)
    • %p-AM or PM
    • %r-Time, 12-hour (hh:mm:ss followed by AM or PM)
    • %S-Seconds (00-59)
    • %s-Seconds (00-59)
    • %T-Time, 24-hour (hh:mm:ss)
    • %U-Week (00-53) where Sunday is the first day of week
    • %u-Week (00-53) where Monday is the first day of week
    • %V-Week (01-53) where Sunday is the first day of week, used with %X
    • %v-Week (01-53) where Monday is the first day of week, used with %x
    • %W-Weekday name (Sunday-Saturday)
    • %w-Day of the week (0=Sunday, 6=Saturday)
    • %X-Year for the week where Sunday is the first day of week, four digits, used with %V
    • %x-Year for the week where Monday is the first day of week, four digits, used with %v
    • %Y-Year, numeric, four digits
    • %y-Year, numeric, two digits

    Example:

    DATE_FORMAT(NOW(),'%d %b %y')
    

    Result:

    13 Jan 17

 

 

Excel formula for Beginners – How to Sum by week number in Excel

 

Learn to Code SQL Example – SQL | Date functions

 

Sign up to get end-to-end “Learn By Coding” example.


 

 

Introduction to Applied Machine Learning & Data Science for Beginners, Business Analysts, Students, Researchers and Freelancers with Python & R Codes @ Western Australian Center for Applied Machine Learning & Data Science (WACAMLDS) !!!

Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:

Applied Statistics with R for Beginners and Business Professionals

Data Science and Machine Learning Projects in Python: Tabular Data Analytics

Data Science and Machine Learning Projects in R: Tabular Data Analytics

Python Machine Learning & Data Science Recipes: Learn by Coding

R Machine Learning & Data Science Recipes: Learn by Coding

 

There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $79.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.

Please do not waste your valuable time by watching videos, rather use end-to-end (Python and R) recipes from Professional Data Scientists to practice coding, and land the most demandable jobs in the fields of Predictive analytics & AI (Machine Learning and Data Science).

The objective is to guide the developers & analysts to “Learn how to Code” for Applied AI using end-to-end coding solutions, and unlock the world of opportunities!

 

Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.