SQL Server Date and Time Functions

The CURRENT_TIMESTAMP function in SQL Server returns the present database system timestamp as a DATETIME value using the 'YYYY-MM-DD hh: mm: ss. mmm' pattern.

The ANSI SQL equivalent of GETDATE is the CURRENT_TIMESTAMP().

This function can be used wherever a DATETIME expression is permitted.

Syntax:

The following is the syntax for the CURRENT_TIMESTAMP Function:

CURRENT_TIMESTAMP

Technical Details

Examples:-

Example: The current date and time are returned in the following example using the CURRENT_TIMESTAMP function:

SELECT CURRENT_TIMESTAMP AS current_date_time;

Result:

The result gives you the current timestamp.

Main Article :- Sql Server DATEADD() Function

The DATEADD() function modifies an input date by adding a number to a given date/time component and returning the result.

Syntax:

The following is the syntax for the DATEADD() Function:

DATEADD(date_part, number, input_date)

Parameter Values

Technical Details

Examples:-

Example 1: Returning the date after adding the seconds, days, and months is demonstrated in this example:

SELECT DATEADD(second, 5, '2022-05-17 23:59:59') AS AddFiveSeconds,
DATEADD(day, 5, '2021-12-27 23:59:59') AS AddFiveDays,
DATEADD(month, 5, '2021-08-16 23:59:59') AS AddFiveMonths;

Result:

In this result, we added 5 seconds, days, and months in the specific date.

Example 2: Returning the date after subtracting the second, day, and months is demonstrated in this example:

SELECT DATEADD(second, -5, '2022-05-17 00:59:55') AS SubtractFiveSeconds,
DATEADD(day, -5, '2022-01-05 23:59:59') AS SubtractFiveDays,
DATEADD(month, 5, '2022-05-11 23:59:59') AS SubtractFiveMonths;

Result:

In this result, we subtractes 5 seconds, days, and months in the specific date.

Example 3: This example to adding year:

Let's use DATESIMPLE table to execute following query:

The query will be like this,

SELECT Name, DOB, 
DATEADD(year, 18, dob) AS AddingYear 
FROM DATESIMPLE;

Result:

From the result, it's adding 18 years in DOB columns.

Main Article :- Sql Server DATEDIFF() Function

The DATEDIFF() function in SQL Server can be used to compute the number of years, months, weeks, days, and so on. It evaluates dates and outputs the difference.

Syntax:

The following is the syntax for the DATEDIFF() Function:

DATEDIFF(interval, start_date, end_date)

Parameter Values

Technical Details

Examples:-

Example 1: The following is an example of how to return the difference in years between two dates:

SELECT DATEDIFF(year, '1999/06/04', '2022/05/16') AS DifferentYear;

Result:

The result outputs years between specified dates. There are 23 years difference between '1999/06/04' and '2022/05/16'.

Example 2: The following is an example of how to return the difference in months between two dates:

SELECT DATEDIFF(month, '1999/06/04', '2022/05/16') AS DifferentDate;

Result:

The result outputs months between specified dates. There are 275 months between '1999/06/04' and '2022/05/16'.

Example 3: The following is an example of how to return the difference in hours between two dates:

SELECT DATEDIFF(hour, '1999/06/04 04:00', '2022/05/16 12:45') AS DifferentHour;

Result:

The result outputs hours between specified dates. There are 201176 hours difference between '1999/06/04 04:00' and '2022/05/16 12:45'.

Example 4: Example of using the DATEDIFF() function with a table column:

Let's take DATEADVANCE table to execute the example:

The query will be look like this,

SELECT Name, 
JoinDate,
LeaveDate,
DATEDIFF(day, JoinDate, LeaveDate) AS date_difference,
DATEDIFF(year, JoinDate, LeaveDate) AS year_difference
FROM DATEADVANCE;

Result:

The result calculates day and year between joindate and leavedate column in the dateadvance table.

To create a date from the year, month, and day, use the SQL Server DATEFROMPARTS() function.

A DATE value is returned by the DATEFROMPARTS() method. The function will return NULL if any parameter is NULL.

Syntax:

The following is the syntax for the DATEFROMPARTS() Function:

DATEFROMPARTS(year, month, day)

Parameter Values

Technical Details

Examples:-

Example 1: The example to construct date parts:

SELECT DATEFROMPARTS(2021, 05, 12) AS ConstructDate;

Result:

From the result you can see that this date parts are constructed on a date.

Example 2: Because the parameter is NULL, the next example returns NULL:

SELECT DATEFROMPARTS(2021,03,NULL) as ConstructedDate;

Result:

If there is null in this function it will return NULL.

To acquire a character string that identifies a particular date component of a date, use the SQL Server DATENAME() function.

This function returns the result as a string value.

Syntax:

The following is the syntax for the DATENAME() Function:

DATENAME(part_of_date, input_date)

Parameter Values

Technical Details

Examples:-

Example 1: This example shows how to get the required portion of a date as a character:

SELECT DATENAME(yy, '2022/09/15') AS YearINDatePartString,
DATENAME(wk, '2022/09/15') AS WeekINDatePartString,
DATENAME(dd, '2022/09/15') AS DayINDatePartString;

Result:

To extract years, weeks, and days component as a character string from a date, use the SQL Server DATENAME() function.

Example 2: This example shows how to get the required portion of a date as a character:

SELECT DATENAME(hh, '2022/09/15 10:15:50') AS HourINDatePartString,
DATENAME(mi, '2022/09/15 10:15:50') AS MinuteINDatePartString,
DATENAME(ss, '2022/09/15 10:15:50') AS SecondINDatePartString;

Result:

To extract a hours, minutes, and seconds component as a character string from a date, use the SQL Server DATENAME() function.

Main Article :- Sql Server DATEPART() Function

The DATEPART() method returns an integer that represents a date component, such as a day, month, or year.

Syntax:

The following is the syntax for the DATEPART() Function:

DATEPART(interval, input_date)

Parameter Values

Technical Details

Examples:-

Example 1: This example shows how to get the required portion of a date as a integer:

SELECT DATEPART(yy, '2022/09/15') AS YearINDatePartInt,
DATEPART(wk, '2022/09/15') AS WeekINDatePartInt,
DATEPART(dd, '2022/09/15') AS DayINDatePartInt;

Result:

To extract years, weeks, and days component as a integer from a date, use the SQL Server DATENAME() function.

Example 2: This example shows how to get the required portion of a date as a integer:

SELECT DATEPART(hh, '2022/09/15 10:15:50') AS HourINDatePartInt,
DATEPART(mi, '2022/09/15 10:15:50') AS MinuteINDatePartInt,
DATEPART(ss, '2022/09/15 10:15:50') AS SecondINDatePartInt;

Result:

To extract a hours, minutes, and seconds component as a integer from a date, use the SQL Server DATENAME() function.

Example 3: The following example uses the DATEPART() function to query the DOB by year, quarter, month and day:

Let's use DATESIMPLE table to execute below query:

The query will be look like this,

SELECT dob, 
DATEPART(year, dob) [year], 
DATEPART(quarter, dob) [quarter], 
DATEPART(month, dob) [month], 
DATEPART(day, dob) [day] 
FROM DATESIMPLE;

Result:

In this result, we used the DATEPART() function to extract year, quarter, month, and day from the values in the dob column.

Main Article :- Sql Server DAY() Function

The DAY() function takes an integer representing the day of a month (1-31) for a given date.

Syntax:

The following is the syntax for the DAY() Function:

DAY(input_date)

Parameter Values

Technical Details

Examples:-

Example 1: Returning the month day from a date is an example:

SELECT DAY('2022/03/31 09:08') AS DayOfMonth;

Result:

To extract the day from the date '2022-03-31', this result uses the DAY() method.

Example 2: The input timestamp for this function is shown in this example:

SELECT DAY('55:45:25') AS DayOfMonth;

Result:

If the provided date exclusively has a time component, the DAY() function will return 1.

Main Article :- Sql Server MONTH() Function

MONTH() provides an integer number that indicates the month of a given date (number from 1 to 12).

Syntax:

The following is the syntax for the MONTH() Function:

MONTH(input_date)

Parameter Values

Technical Details

Examples:-

Example 1: Returning the month from a date is an example:

SELECT MONTH('2022/05/15 09:08') AS MonthOfDate;

Result:

To extract the month from the date '2022-05-15', this result uses the MONTH() method.

Example 2: The input timestamp for this function is shown in this example:

SELECT MONTH('55:45:25') AS DayOfMonth;

Result:

If the provided date exclusively has a time component, the MONTH() function will return 1.

Main Article :- Sql Server YEAR() Function

YEAR() returns an integer result indicating the year of the supplied for a given date.

Syntax:

The following is the syntax for the YEAR() Function:

YEAR(input_date)

Parameter Values

Technical Details

More Examples

Example 1: Returning the year from a date is an example:

SELECT YEAR('2022/05/15 09:08') AS YearOfDate;

Result:

To extract the year from the date '2022-05-15', this result uses the YEAR() method.

Example 2: The input timestamp for this function is shown in this example:

SELECT YEAR('55:45:25') AS YearOfDate;

Result:

If the provided date exclusively has a time component, the YEAR() function will return 1.

The DATETIME value returned by the GETDATE() returns the current database system date and time in the format 'YYYY-MM-DD hh:mm:ss.mmm'.

The DATETIME value is generated from the Operating System (OS) of the server that SQL Server is installed on.

Syntax:

The following is the syntax for the GETDATE() Function:

GETDATE()

Technical Details

Examples:-

Example: Get the database system's current date and time:

SELECT GETDATE() Current_DateTime;

Result:

The result outputs current date and time.

GETUTCDATE() returns the database system's current UTC date and time in the format 'YYYY-MM-DD hh:mm:ss.mmm'.

The GETUTCDATE() function uses the operating system on which the SQL server is running to determine this value.

Syntax:

The following is the syntax for the GETUTCDATE() Function:

GETUTCDATE()

Technical Details

Examples:-

Example: Return the current time and date in UTC with this example:

SELECT GETUTCDATE() as [GetUtcDate];

Result:

The result was used to calculate the current UTC time and date.

To determine whether a value is a valid DATE, TIME, or DATETIME, use the SQL Server ISDATE() function.

The ISDATE() method returns 1 if an expression is a valid date, otherwise 0.

A character string or an expression that resolves to a character string is the expression. It cannot exceed 4,000 characters in length.

Syntax:

The following is the syntax for the ISDATE() Function:

ISDATE(expr)

Parameter Values

Technical Details

Examples:-

Example 1: The example to check the valid date:

SELECT ISDATE('2022-05-15') is_date;

Result:

The function return 1 because the date is valid.

Example 2: The example to check the valid date:

SELECT ISDATE('Welcome Home'),
ISDATE('2022-15-05');

Result:

The function return 0 because the date is invalid.

The SYSDATETIME() method returns the date and time of the SQL Server's host machine.

Because SYSDATETIME() is a nondeterministic function, this function accepts no parameter.

Syntax:

The following is the syntax for the ISDATE() Function:

SYSDATETIME()

Technical Details

Example:

Example: This example shows how to get the date and time from SQL Server:

SELECT SYSDATETIME() AS SysDateTime;

Result:

The result shows that date and time of the system.