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
