Date Functions In SQL Server & MySQL Tutorial Learning Path - Complete Guide
Date Functions In SQL Server And MySQL Tutorial

Date Functions In SQL Server & MySQL Tutorial Learning Path – Complete Guide

Last updated on 01st Jul 2020, Blog, Tutorials

About author

Buvanesh (Azure DevOps Engineer )

Buvanesh is an Azure DevOps Engineer with ten years of experience in BI and Data Science teams, CI/CD with Git, Jenkins and Azure DevOps, PLSQL Actimize, NoSQL Databases, and Data Modelling in Hive, CDH/HDP, CDH or HDP, Spark, Airflow, NiFi, Kafka, Hive, HBase or MongoDB, Neo4J, Elastic Search, Impala, and Sqoop. He spends his precious time researching various technologies and startups.

(5.0) | 18906 Ratings 1216

SQL

SQL is a database computer language designed for the retrieval and management of data in a relational database. SQL stands for Structured Query Language. This tutorial will give you a quick start to SQL. It covers most of the topics required for a basic understanding of SQL and to get a feel of how it works.

Why to Learn SQL?

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.

SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.

    Subscribe For Free Demo

    [custom_views_post_title]

    Also, they are using different dialects, such as −

    • MS SQL Server using T-SQL,
    • Oracle using PL/SQL,
    • MS Access version of SQL is called JET SQL (native format) etc.

    Applications of SQL

    As mentioned before, SQL is one of the most widely used query language over the databases. I’m going to list few of them here:

    • Allows users to access data in the relational database management systems.
    • Allows users to describe the data.
    • Allows users to define the data in a database and manipulate that data.
    • Allows to embed within other languages using SQL modules, libraries & pre-compilers.
    • Allows users to create and drop databases and tables.
    • Allows users to create view, stored procedure, functions in a database.
    • Allows users to set permissions on tables, procedures and views.

    Why SQL?

    SQL is widely popular because it offers the following advantages −

    • Allows users to access data in the relational database management systems.
    • Allows users to describe the data.
    • Allows users to define the data in a database and manipulate that data.
    • Allows to embed within other languages using SQL modules, libraries & pre-compilers.
    • Allows users to create and drop databases and tables.
    • Allows users to create view, stored procedure, functions in a database.
    • Allows users to set permissions on tables, procedures and views.

    SQL Process

    When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.

    There are various components included in this process.

    These components are −

    • Query Dispatcher
    • Optimization Engines
    • Classic Query Engine
    • SQL Query Engine, etc.

    A classic query engine handles all the non-SQL queries, but a SQL query engine won’t handle logical files.

    Following is a simple diagram showing the SQL Architecture −

    SQL-Process

    The following table has a list of all the important Date and Time related functions available through SQL. There are various other functions supported by your RDBMS. The given list is based on MySQL RDBMS.

    Course Curriculum

    Enroll in MySQL Training with Industry Oriented Modules from Expert Instructors

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum
    Files and Description
    ADDDATE()Adds dates
    ADDTIME()Adds time
    CONVERT_TZ()Converts from one timezone to another
    CURDATE()Returns the current date
    CURRENT_DATE(), CURRENT_DATESynonyms for CURDATE()
    CURRENT_TIME(), CURRENT_TIMESynonyms for CURTIME()
    CURRENT_TIMESTAMP(), CURRENT_TIMESTAMPSynonyms for NOW()
    CURTIME()Returns the current time
    DATE_ADD()Adds two dates
    DATE_FORMAT()Formats date as specified
    DATE_SUB()Subtracts two dates
    DATE()Extracts the date part of a date or datetime expression
    DATEDIFF()Subtracts two dates
    DAY()Synonym for DAYOFMONTH()
    DAYNAME()Returns the name of the weekday
    DAYOFMONTH()Returns the day of the month (1-31)
    DAYOFWEEK()Returns the weekday index of the argument
    DAYOFYEAR()Returns the day of the year (1-366)
    EXTRACTExtracts part of a date
    FROM_DAYS()Converts a day number to a date
    FROM_UNIXTIME()Formats date as a UNIX timestamp
    HOUR()Extracts the hour
    LAST_DAYReturns the last day of the month for the argument
    LOCALTIME(), LOCALTIMESynonym for NOW()
    LOCALTIMESTAMP, LOCALTIMESTAMP()Synonym for NOW()
    MAKEDATE()Creates a date from the year and day of year
    MAKETIMEMAKETIME()
    MICROSECOND()Returns the microseconds from argument
    MINUTE()Returns the minute from the argument
    MONTH()Return the month from the date passed
    MONTHNAME()Returns the name of the month
    NOW()Returns the current date and time
    PERIOD_ADD()Adds a period to a year-month
    PERIOD_DIFF()Returns the number of months between periods
    QUARTER()Returns the quarter from a date argument
    SEC_TO_TIME()Converts seconds to ‘HH:MM:SS’ format
    SECOND()Returns the second (0-59)
    STR_TO_DATE()Converts a string to a date
    SUBDATE()When invoked with three arguments a synonym for DATE_SUB()
    SUBTIME()Subtracts times
    SYSDATE()Returns the time at which the function executes
    TIME_FORMAT()Formats as time
    TIME_TO_SEC()Returns the argument converted to seconds
    TIME()Extracts the time portion of the expression passed
    TIMEDIFF()Subtracts time
    TIMESTAMP()With a single argument this function returns the date or datetime expression. With two arguments, the sum of the arguments
    TIMESTAMPADD()Adds an interval to a datetime expression
    TIMESTAMPDIFF()Subtracts an interval from a datetime expression
    TO_DAYS()Returns the date argument converted to days
    UNIX_TIMESTAMP()Returns a UNIX timestamp
    UTC_DATE()Returns the current UTC date
    UTC_TIME()Returns the current UTC time
    UTC_TIMESTAMP()Returns the current UTC date and time
    WEEK()Returns the week number
    WEEKDAY()Returns the weekday index
    WEEKOFYEAR()Returns the calendar week of the date (1-53)
    YEAR()Returns the year
    YEARWEEK()Returns the year and week

    MySQL

    MySQL is a database management system that allows you to manage relational databases. It is open source software backed by Oracle. It means you can use MySQL without paying a dime. Also, if you want, you can change its source code to suit your needs.

    Even though MySQL is open source software, you can buy a commercial license version from Oracle to get premium support services.

    MySQL is pretty easy to master in comparison with other database software like Oracle Database, or Microsoft SQL Server.

     Date Calculations

    MySQL provides several functions that you can use to perform calculations on dates, for example, to calculate ages or extract parts of dates.

    To determine how many years old each of your pets is, use the TIMESTAMPDIFF() function. Its arguments are the unit in which you want the result expressed, and the two dates for which to take the difference. The following query shows, for each pet, the birth date, the current date, and the age in years. An alias (age) is used to make the final output column label more meaningful.

    MySQL Date Functions

    MySQL DATE is a temporal data type for accessing and setting dates by applications. It accepts DATE values only in YYYY-MM-DD format. And MySQL doesn’t allow to change it.

    The DATE format has three subfields: Year, Month, and the date value. And, they should appear in the given order. You won’t be able to use something like MM-DD-YYYY or anything of that sort.

    FunctionDescription
    CURDATEReturns the current date.
    DATEDIFFCalculates the number of days between two DATE values.
    DAYGets the day of the month of a specified date.
    DATE_ADDAdds a time value to date value.
    DATE_SUBSubtracts a time value from a date value.
    DATE_FORMATFormats a date value based on a specified date format.
    DAYNAMEGets the name of a weekday for a specified date.
    DAYOFWEEKReturns the weekday index for a date.
    EXTRACTExtracts a part of a date.
    LAST_DAYReturns the last day of the month of a specified date
    NOWReturns the current date and time at which the statement executed.
    MONTHReturns an integer that represents a month of a specified date.
    STR_TO_DATEConverts a string into a date and time value based on a specified format.
    SYSDATEReturns the current date.
    TIMEDIFFCalculates the difference between two TIME or DATETIME values.
    TIMESTAMPDIFFCalculates the difference between two DATE or DATETIME values.
    WEEKReturns a week number of a date.
    WEEKDAYReturns a weekday index for a date.
    YEARReturn the year for a specified date
    SQL Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    Conclusion

    The first thing we want to do while working with databases is to store our real-world (business) data. In most cases, they’ll contain date and time values. Still, we can also expect that we’ll need to store other date and time values, e.g. when data had been inserted or updated. Working with SQL Server databases without using SQL Server date and time functions is almost impossible. Therefore, store link to this article somewhere to remind yourself of these functions (in case you’re stuck while working with dates & times).

    Tracking date information is important to everyone. Make sure and leverage the MySQL Date and Time functions when dealing with date values for extracting details and insight from dates. 

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free