SQL Server DATE Functions – An Ultimate Guide

Filed Under: SQL Server
SQL Server DATE Functions

Hey, folks! In this article, we will be focusing on the entire set of SQL Server Date Functions in detail. We’ve already learned about the DATEDIFF(), DATEPART(), and other functions.

So, let’s get started!


Need of SQL Server Date Functions

While manipulating or analyzing a certain form of data stored or collected from various sources, the most important aspect is the ‘Timestamp’ i.e. the date and time.

SQL Server Date functions are a set of functions that helps in extracting, manipulating, and analyzing the recorded or system date and time.

Having understood the necessity of SQL Server Date functions and its existence, let us now dive even more deeply to understand various functions within the same.


Extracting the current date and time

At times, we require to use the current date and time for manipulation within our database and code. The following set of functions enables the user to extract and display the current timestamp from the system.

  • GETDATE() function
  • CURRENT_TIMESTAMP() function
  • GETUTCDATE() function
  • SYSDATETIME() function

1. GETDATE() function

SQL Server GETDATE() function accepts no parameter and returns the current date and time on which the SQL Server is running at that point in time.

Syntax:

GETDATE()

Example:

select GETDATE();

Output:

2020-06-05T16:52:40.46Z

2. CURRENT_TIMESTAMP() function

SQL Server CURRENT_TIMESTAMP() function accepts no parameters and returns the current timestamp without taking the timezone into consideration for the output.

Syntax:

CURRENT_TIMESTAMP

Example:

select CURRENT_TIMESTAMP as Date;

Output:

Date
2020-06-05T16:54:21.477Z

3. GETUTCDATE() function

SQL Server GETUTCDATE() function too accepts no parameters and returns the current UTC timestamp as an integer value.

Syntax:

GETUTCDATE()

Example:

select GETUTCDATE();

Output:

2020-06-07T09:34:56.47Z

4. SYSDATETIME() function

SQL Server SYSDATETIME() function returns the current date and time with more precision in terms of miliseconds, etc.

Syntax:

SYSDATETIME()

Example:

select SYSDATETIME();

Output:

2020-06-07 09:37:44.6698077

Extracting a portion of the date and time

SQL Server Date functions include the below list of commands/functions that helps us extract a part of the date or timestamp in terms of the day, month, year, hour, minute, second, week, dayofyear, nanoseconds and so on.

  • DATENAME() function
  • DAY() function
  • MONTH() function
  • YEAR() function
  • DATEPART() funtion

1. DATENAME() function

SQL Server DATENAME() function helps to extract and display a portion of date in terms of the year, month, day, etc as a character string.

That is, the DATENAME() function returns the extracted portion of the date as a character value.

Syntax:

DATENAME(date_portion,input_date)

Example:

select DATENAME(year,'2020/06/16');

In the above example, we have extracted ‘year’ value and represented it as a character string from the input date value.

Output:

2020

2. DAY() function

SQL Server DAY() function accepts the date as input, extracts the day value from the input and returns the day value as an integer.

Syntax:

DAY(input_date)

Example:

select DAY('2020/06/16');

Output:

16

3. MONTH() function

SQL Server MONTH() function accepts the date as input, extracts the month value from the input and returns it as an integer.

Syntax:

MONTH(input_date)

Example:

select MONTH('2020/06/16');

Output:

6

4. YEAR() function

SQL Server YEAR() function accepts the date as input, extracts the year value from the input and returns the year value as an integer.

Syntax:

YEAR(input_date)

Example:

select YEAR('2020/06/16');

Output:

2020

5. DATEPART() function

SQL Server DATEPART() is very similar to DATENAME() function with a single point of difference.

The DATEPART() function accepts the date portion to be extracted and the input date. It returns the extracted date portion as an integer value unlike DATENAME() function which returns the extracted portion as a character string.

Syntax:

DATEPART(date_portion,input_date)

Example:

select DATEPART(month,'2020/06/16');

Output:

6

Manipulation of date and time

SQL Server Date functions include the following set of functions to manipulate i.e. make changes to the system/input timestamp values.


1. DATEADD() function

SQL Server DATEADD() function helps us add an integer value to the date_portion of the input date passed to it.

For more details about DATEADD() function, please do visit SQL Server DATEADD() function.

Syntax:

DATEADD(date_portion,value,inout_date)

Example:

select DATEADD(day,14,'2020/06/16');

Output:

2020-06-30T00:00:00Z

2. DATEDIFF() function

SQL Server DATEDIFF() function is used to return the difference between the date_portions of two input date values passed to it.

Syntax:

DATEDIFF(date_portion,input_date1,input_date2)

Example:

select DATEDIFF(month,'2020/01/12','2020/06/16');

Output:

5

Constructing date and time

SQL Server Date functions include some set of functions that help us construct or build date in a standard format(yyyy-mm-dd) from the data provided to the function.

  • DATEFROMPARTS() function
  • TIMEFROMPARTS() function

1. DATEFROMPARTS() function

SQL Server DATEFROMPARTS() function is used to construct a date from the various date portions provided as parameters.

Syntax:

DATEFROMPARTS(year,month,day)

Example:

select DATEFROMPARTS(2020, 06, 14);

Output:

2020-06-14

2. TIMEFROMPARTS() function

SQL Server TIMEFROMPARTS() function builds time in the standard for from the input parameters as below–

  • hour
  • minute
  • second
  • fraction
  • precision value

Note: The precision value can never be NULL.

Syntax:

TIMEFROMPARTS(hour,minute,second,fraction,precision)

Example:

SELECT TIMEFROMPARTS(7, 25, 33, 0, 0);

Output:

07:25:33

Validation of date and time

In SQL Server, the valid format of date is ‘yyyy-mm-dd‘. SQL Server ISDATE() function is used to check whether the entered date follows the standard format or not.

Syntax:

ISDATE(input_date)

The ISDATE() function returns 1, if the date follows the standard format. If the format of date differs, it returns 0.

Example 1:

SELECT ISDATE('2020-06-14') AS Valid_Date

Output:

Valid_date
1

Example 2:

SELECT ISDATE('2020-14-06') AS Valid_Date

Output:

Valid_Date
0

Conclusion

By this, we have come to the end of this topic. Please feel free to comment below, incase you come across any doubt.

For more such topics related to SQL Server, please do visit SQL Server JournalDev.


References

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages