Dates
in SQL are difficult for beginners to understand since, while working with
databases, the format of the date in the table must match the input date in
order to insert. Datetime is sometimes used in place of dates because dates and
times are related.
Some of
the crucial date-related features were already covered in the last post. The
main goal of this article is to familiarise readers with the syntax and
operation of all date functions:DateTime functions in SQL Server
DateTime
functions in SQL Server
In this
post, we will learn about.
1.
DateTime data types
2.
DateTime functions are available to select the current system date and time
3.
Understanding concept - UTC time and Time Zone
There
are many built-in DateTime functions available in SQL Server. All of the
following functions can be used to get the current system date and time, where
you have SQL Server installed.
Function Date Time Format Description
GETDATE() 2012-08-31 20:15:04.543 Commonly used function
CURRENT_TIMESTAMP 2012-08-31 20:15:04.543 ANSI SQL equivalent to GETDATE
SYSDATETIME() 2012-08-31 20:15:04.5380028 More fractional seconds precision
SYSDATETIMEOFFSET() 2012-08-31 20:15:04.5380028 + 01:00 More fractional seconds precision + Time
zone offset
GETUTCDATE() 2012-08-31 19:15:04.543 UTC Date and Time
SYSUTCDATETIME() 2012-08-31 19:15:04.5380028 UTC Date and Time, with More fractional
seconds precision
Note:
UTC stands for Coordinated Universal Time, on the basis of which the world
regulates clocks and time. There is little difference between GMT and UTC, but
for most general purposes, UTC is synonymous with GMT..
To
practically understand how the different date-time data types available in SQL
Server, store data, create the sample table tblDateTime.
CREATE
TABLE [tblDateTime]
(
[c_time]
[time](7) NULL,
[c_date]
[date] NULL,
[c_smalldatetime]
[smalldatetime] NULL,
[c_datetime]
[datetime] NULL,
[c_datetime2]
[datetime2](7) NULL,
[c_datetimeoffset]
[datetimeoffset](7) NULL
)
To
Insert sample data, execute the following query.
INSERT
INTO tblDateTime VALUES
(GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE(),GETDATE())
Now,
issue a SELECT statement, and you should see a variety of DateTime datatypes,
storing the current date time, in various formats.
ADDDATE():After
a specific time or date interval has been added, it returns a date.
Syntax: SELECT ADDTIME("2018-07-16 02:52:47", "2");
Output: 2018-07-16 02:52:49
ADDTIME(): After a specific amount of time has been added, it returns a
time/date.
Syntax: SELECT ADDTIME("2017-06-15 09:34:21", "2");
Output: 2017-06-15 09:34:23
CURDATE(): It returns the current date.
Syntax: SELECT CURDATE();
Output: 2018-07-16
CURRENT_DATE(): It gives back the current date.
Syntax: SELECT CURRENT_DATE();
Output: 2018-07-16
CURRENT_TIME(): It gives back the current time and date.
Syntax: SELECT CURRENT_TIME();
Output: 02:53:15
CURRENT_TIMESTAMP(): It gives back the current date and time.
Syntax: SELECT CURRENT_TIMESTAMP();
Output: 2018-07-16 02:53:21
CURTIME(): It returns the current time.
Syntax: SELECT CURTIME();
Output: 02:53:28
DATE(): It extracts the date value from a date or date time expression.
Syntax: SELECT DATE("2017-06-15");
Output: 2017-06-15
DATEDIFF(): It returns the difference in days between two date values.
Syntax: SELECT DATEDIFF("2017-06-25", "2017-06-15");
Output: 10
DATE_ADD(): It gives back a date after a certain time/date interval has been
added.
Syntax: SELECT DATE_ADD("2018-07-16", INTERVAL 10 DAY);
Output: 2018-07-16
DATE_FORMAT(): It formats a date in accordance with a format mask.
Syntax: SELECT DATE_FORMAT("2018-06-15", "%Y");
Output: 2018
DATE_SUB(): It gives back a date after a certain time/date interval has been
subtracted.
Syntax: SELECT DATE_SUB("2017-06-15", INTERVAL 10 DAY);
Output: 2018-07-16
DAY(): It gives back the day portion of a date value.
Syntax: SELECT DAY("2018-07-16");
Output: 16
DAYNAME(): It returns the weekday name for a date.
Syntax: SELECT DAYNAME('2008-05-15');
Output: Thursday
DAYOFMONTH(): It returns the day portion of a date value.
Syntax: SELECT DAYOFMONTH('2018-07-16');
Output: 16
DAYWEEK(): It returns the weekday index for a date value.
Syntax: SELECT WEEKDAY("2018-07-16");
Output: 0
DAYOFYEAR(): It returns the day of the year for a date value.
Syntax: SELECT DAYOFYEAR("2018-07-16");
Output: 197
EXTRACT(): It extracts parts from a date.
Syntax: SELECT EXTRACT(MONTH FROM "2018-07-16");
Output: 7
FROM_DAYS(): It gives back a date value from a numeric representation of the
day.
Syntax: SELECT FROM_DAYS(685467);
Output: 1876-09-29
HOUR(): It returns the hour portion of a date value.
Syntax: SELECT HOUR("2018-07-16 09:34:00");
Output: 9
LAST_DAY(): It gives back the last day of the month for a given date.
Syntax: SELECT LAST_DAY('2018-07-16');
Output: 2018-07-31
LOCALTIME(): It returns the current date and time.
Syntax: SELECT LOCALTIME();
Output: 2018-07-16 02:56:42
LOCALTIMESTAMP(): It returns the current date and time.
Syntax: SELECT LOCALTIMESTAMP();
Output: 2018-07-16 02:56:48
MAKEDATE(): It returns the date for a certain year and day-of-year value.
Syntax: SELECT MAKEDATE(2009, 138);
Output: 2009-05-18
MAKETIME(): It returns the time for a certain hour, minute, second combination.
Syntax: SELECT MAKETIME(11, 35, 4);
Output: 11:35:04
