Date and time processing functions

This article will summarize the functions of SQL in date and time processing. In summary, the following functions are mainly used in our development process.

1. Get the current system time

2. dateadd Function

3. datediff Function

4. datepart Function

5, year, month, and day Functions

6. datename Function

7. isdate Function

Obtain the current system time

There are many functions to obtain the current system time, but the two most common functions are current_timestamp and getdate. They return the same date and time format, but because current_timestamp is a standard SQL, we recommend that you use it.

SQL query code:

SELECT GETDATE() AS [GETDATE], CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP];

Query results:

We can see that they return the same date and time.

Dateadd Function

The dateadd function uses the specified date as the Unit to increase the specified number of input date and time values. Its Syntax format is: dateadd (part, N, dt_val)

For example, the following code adds one year to July 26, 2014.

SELECT DATEADD(YEAR,1,‘20140726‘);

Query results:

Datediff Function

The datediff function returns the count of the specified part of the difference between two dates and time. Its Syntax format: datediff (part, dt_val1, dt_val2)

For example, the following code returns the number of days between two values.

SELECT DATEDIFF(DAY,‘20130726‘,‘20140726‘);

Query results:

We can see that they are related for 365 days.

Datepart Functions

The datepart function returns an integer that represents the specified part of the given date and time value. Syntax: datepart (part, dt_val)

For example, the following code returns the month of the input value.

SELECT DATEPART(MONTH,‘20140726‘);

Query results:

The month of the current date returned by the Code is July.

Year, month, and day Functions

Year, month, and day functions are a brief version of The datepart function. They return an integer representing the input Date and Time Value of the middle, month, and day. Syntax: Year (dt_val), month (dt_val), Day (dt_val)

For example, the following code extracts the year, month, and day of the input value.

USE TSQLFundamentals2008;GOSELECT    YEAR(‘20140726‘) AS theyear,    MONTH(‘20140726‘) AS themonth,    DAY(‘20140726‘) AS theday;

Query results:

Datename Function

The datename function returns a string of the specified part of the given date and time value. Syntax: datename (part, dt_val)

For example, the following code returns the month name string for a given input date and time value.

SELECT DATENAME(MONTH,‘20140726‘);

Query results:

Isdate Function

The isdate function accepts a string as the input. If the string can be converted to a value of the date and time data type, 1 is returned. If not, 0 is returned. Its Syntax: isdate (string)

For example, the following code returns 1:

The following code returns 0:

Use tsqlfundamentals2008; go -- returns 1, which can be converted to select isdate ('201312'); -- returns 0. Because there is no 30 on January 1, 20090212, The Conversion failed to select isdate ('2016030 ');

Query results:

1 and 0 can be converted to true and false in C.

Date and time processing functions