Date Functions In SQL Server And MySQL Tutorial

Date Functions In SQL Server And MySQL Tutorial

Last updated on 12th Oct 2020, Blog, Tutorials

About author

Aravind (TL / Sr Engineer - SQL DBA )

He is a Award Winning Respective Industry Expert with 11+ Years Of Experience Also, He is a TOP Rated Technical Blog Writer Share's 1000+ Blogs for Freshers. Now He Share's this For Us.

(5.0) | 14257 Ratings 2153

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 the 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

Error: Contact form not found.

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 languages over 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 −

handle-logical-files

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 forCURDATE()CURRENT_TIME(),
  • CURRENT_TIMESynonyms forCURTIME()CURRENT_TIMESTAMP(),
  • CURRENT_TIMESTAMPSynonyms forNOW()
  • 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 yearMAKETIMEMAKETIME()
  • 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 temporary data type for accessing and setting dates by applications. It accepts DATE values only in YYYY-MM-DD format. And MySQL doesn’t allow me 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.

Sql Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

Function

Description

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

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 has been inserted or updated. Working with SQL Server databases without using SQL Server date and time functions is almost impossible. Therefore, store a 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

Popular Courses