SQL Server Date and Time related Interview Questions

This post can help you in answering SQL Server Date and Time related Interview Questions.  Below is the list of queries which are recently asked in SQL Server Developer interviews. These are not just for interview preparation also helps you as a quick reference while working on date & time related queries.

Along with these it would be good if you can refer the most commonly used DATETIME data types (DATE, TIME, SMALLDATETIME, DATETIME, DATETIME2, and DATETIMEOFFSET) and functions (GETDATE, SYSDATETIME, CONVERT, DATENAME, DATEPART, DATEDIFF, DATEADD, EOMONTH etc.)

/**********************************************************************/
/*************** SQL Server Date and Time Queries *********************/
/**********************************************************************/

------------------------- Date ------------------------

-- Retireve Current Date and Time
SELECT GETDATE() 'Today'

-- Retrieve Yesterday Date
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'

------------------------- Week ------------------------

-- Get the First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) AS 'Current_Week_First_Day'

-- Get the Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) AS 'Current_Week_Last_Day';

-- Get the First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) AS 'Last_Week_First_Day';

-- Get the Last Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) AS 'Last_Week_Last_Day';

------------------------- Month ------------------------

-- Get the First Day of Current Month
SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) AS 'Current_Month_First_Day';

-- Get the Last Day of Current Month
SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) AS 'Current_Month_Last_Day';

-- Get the First Day of Last Month
SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) AS 'Last_Month_First_Day'

-- Get the Last Day of Last Month
SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) AS 'Last_Month_Last_Day'

------------------------- Quarter ------------------------

-- Get the First Day of Current Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate()), 0) AS 'Current_Quarter_First_Day'

-- Get the Last Day of Current Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate())+1, 0)) AS 'Current_Quarter_Last_Day'

-- Get the First Day of Last Quarter
SELECT Dateadd(qq, Datediff(qq,0,GetDate())-1, 0) AS 'Last_Quarter_First_Day'

-- Get the Last Day of Last Quarter
SELECT Dateadd(ms,-3,Dateadd(qq, Datediff(qq,0,GetDate()), 0)) AS 'Last_Quarter_Last_Day'

------------------------- Year ------------------------

-- Get the First Day of Current Year
SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) AS 'Current_Year_First_Day'

-- Get the Last Day of Current Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE())+1,0))) AS 'Current_Year_Last_Day'

-- Get the First Day of Last Year
SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)) AS 'Last_Year_First_Day'

-- Get the Last Day of Last Year
SELECT DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))) AS 'Last_Year_Last_Day'

Logic Explained:

When you are asked to write a query in an interview, you need not give the exact output rather you are expected to give the logic. The person who understands the base logic can easily implement a solution for any given requirement. Now let’s try to understand the logic.

Here ‘0’ indicates the starting date in the data time range. i.e : 1900-01-01 00:00:00.000

SELECT DATEADD (DD, 0, 0);

Now we’ll see how to get the first day of current week:

SELECT DATEADD (wk, DATEDIFF (wk, 0, GETDATE ()),0) AS ‘Current_Week_First_Day’

DATEDIFF (wk, 0, GETDATE()): First we are capturing the total number of weeks between the start date in date range (1900-01-01) and the current date (Based on the GTEDATE). For example today’s date is 24-Jan-17 then the difference will be (SELECT DATEDIFF (wk, 0, ‘2017-01-24’) ; 6108.

DATEADD (wk, DATEDIFF (wk, 0, GETDATE ()), 0): We have calculated the inner value as 6108. Now we simply add the number of weeks to the start date then it fetch the 6108 week starting date. i.e.: SELECT DATEADD (wk, 6108, 0); returns the week start date as “2017-01-23 00:00:00.000”

SQL Server Date and Time related Interview Questions  Download the T-SQL script file.