In SQL, the DateTime data type is used to work with dates and times. SQL Server comes with numerous functions for processing dates and times. Some of these include GETDATE(), DATEDIFF(), DATEADD(), CONVERT(), and so forth.

GETDATE()
GETDATE() returns the current date and time as a DateTime datatype. It does not require any arguments.

SELECT GETDATE() AS CurrentDateTime;

DATEDIFF()

DATEDIFF() returns the difference between two date values based on the unit of time you want to use. The syntax is DATEDIFF(datepart, startdate, enddate).

SELECT DATEDIFF(day, '2022-01-01', '2022-01-15') AS DiffInDays;

DATEADD()

DATEADD() adds or subtracts a specified time interval from a date. Its syntax is DATEADD(datepart, number, date).

SELECT DATEADD(year, 1, '2022-01-01') AS NewDate;

CONVERT()

CONVERT() is used to convert from one data type to another, and it is commonly used to format DateTime values. Its syntax is CONVERT(data_type(length), expression, style).

SELECT CONVERT(VARCHAR(19), GETDATE()) AS FormattedDateTime;

Remember to replace date with your date in above queries.

DateTime Format
By using appropriate format codes, SQL allows us to present dates and times in various formats.

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy') AS DateFormatted;

TIME

SELECT ADDTIME('2007-12-31 23:59:59','1 1:1:1');

Let's break down the components of the interval:

1 day
1 hour
1 minute
1 second
So, when you add this interval to '2007-12-31 23:59:59', the result will be '2008-01-02 01:01:00'. The date is incremented by 1 day, the time by 1 hour, 1 minute, and 1 second.

Therefore, the output of the given query is '2008-01-02 01:01:00'.

SELECT TIME_TO_SEC('22:23:00');

The TIME_TO_SEC function in MySQL is used to convert a time value to seconds. In the query you provided, you are converting the time '22:23:00' to seconds.

DATEPART

DATEPART is a useful function in SQL that allows you to extract a specific part of a date or time field. You can use it to get the year, quarter, month, day of the year, day, week, weekday, hour, minute, second, or millisecond from any date or time expression.

DATEPART(datepart, date)

SELECT DATEPART(hour, '2021-07-14T13:30:15') AS 'Hour',
       DATEPART(minute, '2021-07-14T13:30:15') AS 'Minute',
       DATEPART(second, '2021-07-14T13:30:15') AS 'Second';

DATEADD

DATEADD is a built-in function in SQL that allows you to add or subtract units of time from a specified date. The function takes three parameters:

An interval type (such as day, month, year, hour, minute, second)
A number (which can be either positive, for future dates, or negative, for past dates)
A date from which calculation will be based.

The usage of this function can be especially useful

DATEADD(interval, number, date)

SELECT DATEADD(day, 3, '2022-01-01') as NewDate

Output -> 2022-01-04

Sample Query

If you have a table called Orders with a DateTime field OrderDate and you want to find all orders placed in the next seven days, you can use the DATEADD function as follows:

SELECT * FROM Orders
WHERE OrderDate <= DATEADD(day, 7, GETDATE())

This will return all orders from now until a week from now.

TIMESTAMP

SQL TIMESTAMP is a data type that allows you to store both date and time. It is typically used to track updates and changes made to a record, providing a chronological time of happenings.

CREATE TABLE table_name (
   column1 TIMESTAMP,
   column2 VARCHAR(100),
   ...
);

A common use-case of TIMESTAMP is to have an automatically updated timestamp each time the row is updated. This can be achieved by setting the DEFAULT constraint to CURRENT_TIMESTAMP:

CREATE TABLE table_name (
   column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   column2 VARCHAR(100),
   ...
);

In MySQL, ON UPDATE CURRENT_TIMESTAMP can be used to automatically update the TIMESTAMP field to the current date and time whenever there is any change in other fields of the row.

CREATE TABLE table_name (
   column1 TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   column2 VARCHAR(100),
   ...
);

You can also insert or update records with a specific timestamp:

INSERT INTO table_name (column1, column2) VALUES ('2019-06-10 10:20:30', 'example data');

UPDATE table_name SET column1 = '2020-07-20 15:30:45' WHERE column2 = 'example data';

Date and Time