Date Functions In SQL Server & MySQL Tutorial Learning Path – Complete Guide
Last updated on 01st Jul 2020, Blog, Tutorials
(5.0) | 18906 Ratings
1218
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.
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 −
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.
Enroll in MySQL Training with Industry Oriented Modules from Expert Instructors
- Instructor-led Sessions
- Real-life Case Studies
- Assignments
| 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.
| Function | Description |
| CURDATE | Returns the current date. |
| DATEDIFF | Calculates the number of days between two DATE values. |
| DAY | Gets the day of the month of a specified date. |
| DATE_ADD | Adds a time value to date value. |
| DATE_SUB | Subtracts a time value from a date value. |
| DATE_FORMAT | Formats a date value based on a specified date format. |
| DAYNAME | Gets the name of a weekday for a specified date. |
| DAYOFWEEK | Returns the weekday index for a date. |
| EXTRACT | Extracts a part of a date. |
| LAST_DAY | Returns the last day of the month of a specified date |
| NOW | Returns the current date and time at which the statement executed. |
| MONTH | Returns an integer that represents a month of a specified date. |
| STR_TO_DATE | Converts a string into a date and time value based on a specified format. |
| SYSDATE | Returns the current date. |
| TIMEDIFF | Calculates the difference between two TIME or DATETIME values. |
| TIMESTAMPDIFF | Calculates the difference between two DATE or DATETIME values. |
| WEEK | Returns a week number of a date. |
| WEEKDAY | Returns a weekday index for a date. |
| YEAR | Return the year for a specified date |
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.