DATE_TIME functions in Sql server

— Function to get system’s date and time

SELECT SYSDATETIME(); — function to get system’s current date and time with decimal precision of 7 digits

2021–04–22 10:45:03.4318758

select CURRENT_TIMESTAMP;

select GETDATE(); — function to get system’s current date and time with decimal precision of 3 digits

SELECT SYSDATETIMEOFFSET(); — function to get system’s current date and time + offset

2021–04–22 10:46:11.1008493 +05:30

SELECT SYSUTCDATETIME(); — function to get system’s current date and time in UTC format with decimal precision of 7 digits

2021–04–22 05:17:27.1006020

SELECT GETUTCDATE(); — function to get system’s current date and time in UTC format with decimal precision of 3 digits

2021–04–22 05:24:12.270

— Function Datename and Datepart

— DATENAME — returns a string corresponding to the datepart specified

— DATEPART — Used to extract part of a string

SELECT DATENAME(YEAR, GETDATE()) AS ‘Year’; or

SELECT DATEPART(YEAR, GETDATE()) AS ‘Year’;

2021

SELECT DATENAME(QUARTER, GETDATE()) AS ‘QUARTER’; or

SELECT DATEPART(QUARTER, GETDATE()) AS ‘QUARTER’;

2

SELECT DATENAME(MONTH, GETDATE()) AS ‘MONTH’; — returns string

April

SELECT DATEPART(MONTH, GETDATE()) AS ‘MONTH’; — returns int

4

SELECT DATENAME(DAYOFYEAR, GETDATE()) AS ‘DayOfYear’; or

SELECT DATEPART(DAYOFYEAR, GETDATE()) AS ‘DayOfYear’;

112

SELECT DATENAME(DAY, GETDATE()) AS ‘Day’; or

SELECT DATEPART(DAY, GETDATE()) AS ‘Day’;

22

SELECT DATENAME(WEEK, GETDATE()) AS ‘Week’; or

SELECT DATEPART(WEEK, GETDATE()) AS ‘Week’;

17

SELECT DATENAME(WEEKDAY, GETDATE()) AS ‘WeekDay’; — returns string

Thursday

SELECT DATEPART(WEEKDAY, GETDATE()) AS ‘WeekDay’; — returns int

5

SELECT DATENAME(HOUR, GETDATE()) AS ‘Hour’; or

SELECT DATEPART(HOUR, GETDATE()) AS ‘Hour’;

11

SELECT DATENAME(MINUTE, GETDATE()) AS ‘Minute’; or

SELECT DATEPART(MINUTE, GETDATE()) AS ‘Minute’;

24

SELECT DATENAME(SECOND, GETDATE()) AS ‘Second’;

14

— Date and Time Difference two dates

— DATEDIFF Function used in sql server to subtract two dates always 3 parameter’s this function

SELECT DATEDIFF(DAY, 2019–31–01, 2019–01–01) AS ‘DateDif’; — returns int

30

SELECT DATEDIFF_BIG(DAY, 2019–31–01, 2019–01–01) AS ‘DateDifBig’; — returns bigint

30

— DATEADD() Function adds a number to a specified date part of an input date and returns the modified value.

SELECT DATEADD(second, 1, CURRENT_TIMESTAMP) as result;

SELECT DATEADD(day, 1, CURRENT_TIMESTAMP) as result;

SELECT DATEADD(hour, 1, CURRENT_TIMESTAMP) as result;

SELECT DATEADD(minute, 1, CURRENT_TIMESTAMP) as result;

— Function ISDATE() — check whether date is valid or not if valid returns 1 else 0

SELECT ISDATE(‘2020–02–31’) is_date;

0

SELECT ISDATE(‘2020–01–31’) is_date;

1

Database Engineer having working experience in SQL and NO-SQL databases. 👨‍💻