To find the difference between two dates in SQL Server

To find the difference between two dates.


SELECT DATEDIFF(DAY, '4/29/2019', '6/14/2019');

result : 46

SELECT DATEDIFF(DAY, '5/09/2019', '6/14/2019');

Result: 36

SELECT DATEDIFF(DAY, '4/29/2019', SYSDATETIME());

Result: 78

To find the difference between two dates in SQL Server

To find the difference between two dates.


SELECT DATEDIFF(DAY, '4/29/2019', '6/14/2019');

result : 46

SELECT DATEDIFF(DAY, '5/09/2019', '6/14/2019');

Result: 36

SELECT DATEDIFF(DAY, '4/29/2019', SYSDATETIME());

Result: 78

SQL Server DATENAME Function

DATENAME – returns a string corresponding to the datepart specified

SELECT DATENAME(YEAR, GETDATE())        AS 'Year';        
SELECT DATENAME(QUARTER, GETDATE())     AS 'Quarter';     
SELECT DATENAME(MONTH, GETDATE())       AS 'Month';       
SELECT DATENAME(DAYOFYEAR, GETDATE())   AS 'DayOfYear';   
SELECT DATENAME(DAY, GETDATE())         AS 'Day';         
SELECT DATENAME(WEEK, GETDATE())        AS 'Week';        
SELECT DATENAME(WEEKDAY, GETDATE())     AS 'WeekDay';     
SELECT DATENAME(HOUR, GETDATE())        AS 'Hour';        
SELECT DATENAME(MINUTE, GETDATE())      AS 'Minute';      
SELECT DATENAME(SECOND, GETDATE())      AS 'Second';      
SELECT DATENAME(MILLISECOND, GETDATE()) AS 'MilliSecond'; 
SELECT DATENAME(MICROSECOND, GETDATE()) AS 'MicroSecond'; 
SELECT DATENAME(NANOSECOND, GETDATE())  AS 'NanoSecond';  
SELECT DATENAME(ISO_WEEK, GETDATE())    AS 'Week';        

SQL Server DATENAME Function

DATENAME – returns a string corresponding to the datepart specified

SELECT DATENAME(YEAR, GETDATE())        AS 'Year';        
SELECT DATENAME(QUARTER, GETDATE())     AS 'Quarter';     
SELECT DATENAME(MONTH, GETDATE())       AS 'Month';       
SELECT DATENAME(DAYOFYEAR, GETDATE())   AS 'DayOfYear';   
SELECT DATENAME(DAY, GETDATE())         AS 'Day';         
SELECT DATENAME(WEEK, GETDATE())        AS 'Week';        
SELECT DATENAME(WEEKDAY, GETDATE())     AS 'WeekDay';     
SELECT DATENAME(HOUR, GETDATE())        AS 'Hour';        
SELECT DATENAME(MINUTE, GETDATE())      AS 'Minute';      
SELECT DATENAME(SECOND, GETDATE())      AS 'Second';      
SELECT DATENAME(MILLISECOND, GETDATE()) AS 'MilliSecond'; 
SELECT DATENAME(MICROSECOND, GETDATE()) AS 'MicroSecond'; 
SELECT DATENAME(NANOSECOND, GETDATE())  AS 'NanoSecond';  
SELECT DATENAME(ISO_WEEK, GETDATE())    AS 'Week';        

SQL server date functions

To find current date and time : select SYSDATETIME();

To display only date : select convert(date,SYSDATETIME());

To display only time : select convert(time,SYSDATETIME());

Here is the query to display all this above.

select SYSDATETIME() as Timestamp,convert(date,SYSDATETIME()) as Date,CONVERT(time,SYSDATETIME()) as Time;

SQL server date functions

To find current date and time : select SYSDATETIME();

To display only date : select convert(date,SYSDATETIME());

To display only time : select convert(time,SYSDATETIME());

Here is the query to display all this above.

select SYSDATETIME() as Timestamp,convert(date,SYSDATETIME()) as Date,CONVERT(time,SYSDATETIME()) as Time;