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.
- DATEADD() function
- DATEDIFF() function
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.