Date Related Tutorial and SQL Server Interview Questions

Date and Time Data Types and Functions :

Date and Time Data Types :

Data type   Format Storage size (bytes) time hh:mm:ss[.nnnnnnn] 3 to 5 date YYYY-MM-DD  3 smalldatetime YYYY-MM-DD hh:mm:ss 4 datetime YYYY-MM-DD hh:mm:ss[.nnn] 8 datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn] 6 to 8 datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 8 to 10

 Date and Time Functions :

Functions That Get System Date and Time Values:-

Higher-Precision System Date and Time Functions :

Function : SYSDATETIME

Syntax : SYSDATETIME()

Return Type : datetime2(7)

Examples :

A. Getting the current system date and time.

SELECT SYSDATETIME(

Output : 2015-03-16 14:42:13.800

B. Getting the current system date

SELECT CONVERT (date, SYSDATETIME())
Output : 2015-03-16

C. Getting the current system time

SELECT CONVERT (time, SYSDATETIME()
Output : 14:52:04.2297101
-----------------------------------------------------------------------------------------
Function : SYSDATETIMEOFFSET

Syntax : SYSDATETIMEOFFSET()

Return Type : datetimeoffset(7)

Examples :

A. Showing the formats that are returned by the date and time functions.

SELECT SYSDATETIMEOFFSET())

Output: 2015-03-16 15:03:12.3632621 +05:30

B. Converting date and time to date.

SELECT CONVERT (date, SYSDATETIMEOFFSET())

Output: 2015-03-16

C. Converting date and time to times.

SELECT CONVERT (time, SYSDATETIMEOFFSET())

Output: 15:05:41.5057961
------------------------------------------------------------------------------------------
Function : SYSUTCDATETIME

Syntax : SYSUTCDATETIME ( )

Return Type : datetime2

Examples :

A. Showing the formats that are returned by the date and time functions.

SELECT SYSUTCDATETIME ( )

Output: 2015-03-16 09:42:20.7452813

B. Converting date and time to date.

SELECT CONVERT (date, SYSUTCDATETIME())
Output: 2015-03-16

C. Converting date and time to times.

SELECT CONVERT (time, SYSUTCDATETIME())

Output: 09:43:36.1368713
-----------------------------------------------------------------------------------------

Lower-Precision System Date and Time Functions

Function : CURRENT_TIMESTAMP

Syntax : CURRENT_TIMESTAMP

Return Type : datetime

Examples :

A. Get the Current System Date and Time.

SELECT CURRENT_TIMESTAMP

Output:2015-03-16 15:36:48.997

B. Get the Current System Date.

SELECT CONVERT (date, CURRENT_TIMESTAMP)

Output:2015-03-16

C. Get the Current System Time.

SELECT CONVERT (time, CURRENT_TIMESTAMP)

Output: 15:38:57.3570000
--------------------------------------------------------------------------------------
Function : GETDATE

Syntax : GETDATE ( )

Return Type : datetime

Examples :

A. Get the Current System Date and Time.

SELECT GETDATE ( )

Output:2015-03-16 15:42:49.987

B. Get the Current System Date.

SELECT CONVERT (date, GETDATE ( ))

Output:2015-03-16

C. Get the Current System Time.

SELECT CONVERT (time, GETDATE ( ))

Output: 15:45:37.1600000
---------------------------------------------------------------------------------------
Function : GETUTCDATE

Syntax : GETUTCDATE()

Return Type : datetime

Examples :

A. Getting the current system date and time

SELECT GETUTCDATE()

Output: 2015-03-17 08:02:37.123

B. Getting the current system date

SELECT  CONVERT (date, GETUTCDATE());

Output: 2015-03-17

C. Getting the current system time

SELECT  CONVERT (time, GETUTCDATE());

Output: 08:04:43.3900000



Functions That Get Date and Time Parts:-

Function : DATENAME

Syntax : DATENAME ( datepart , date )

Return Type : nvarchar

Arguments : Below are the list of datepart arguments.

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns
TZoffset	tz
ISO_WEEK	ISOWK, ISOWW

Examples :

SELECT DATENAME(YEAR,'2007-10-30 12:15:32.1234567 +05:10');

 Below is the result:

datepart	Return value
year	2007
quarter	4
month	October
dayofyear	303
day	30
week	44
weekday	Tuesday
hour	12
minute	15
second	32
millisecond	123
microsecond	123456
nanosecond	123456700
TZoffset	310
ISO_WEEK	44

Function : DATEPART

Syntax : DATEPART ( datepart , date )

Return Type : int

Examples :

SELECT DATEPART(DAYOFYEAR,GETDATE())
Output :77

Note : Try with other arguments also for practice.

Function : DAY

Syntax : DAY (date )

Return Type : int

Examples :
SELECT DAY(GETDATE()) If date is 18th March 2015
Output :18


1. Write a SQL Query to show JoiningDate in "DD MMM YYYY" format from employee table. Ex: "15 Feb 2015".

Ans: Select convert ( varchar(20), JoiningDate, 106) from employee.

2. Write a SQL Query to show JoiningDate in "yyyy/mm/dd" format from employee table.
    Ex: 2015/02/15

Ans: Select convert ( varchar(20), JoiningDate, 111) from employee.

3. Write a SQL Query to show only time part of JoiningDate from employee table.

Ans: Select convert ( varchar(20), JoiningDate, 108) from employee.

4. Write a SQL Query to show only Year part of JoiningDate from employee table.

Ans: Select datepart ( Year, JoiningDate) from employee.

5. Write a SQL Query to show only Month part of JoiningDate from employee table.

Ans: Select datepart ( month, JoiningDate) from employee.

6. Write a SQL Query to get System Date.

Ans: Select Getdate().

7. Write a SQL Query to get UTC Date.

Ans: Select Getutcdate().

8. Write a SQL Query to get firstname, current date, joiningdate and difference between current date and joining date in months.

Ans: Select firstname, getdate() as current date, joiningdate, datediff( mm, joiningdate, getdate()) as totalmonths from employee.

9. Write a SQL Query to get firstname, current date, joiningdate and difference between current date and joining date in days.

Ans: Select firstname, getdate() as current date, joiningdate, datediff( dd, joiningdate, getdate()) as totaldays from employee.

10. Write a SQL Query to get employee details from employee table whose joining year is 2015.

Ans: Select * from employee where datepart (yyyy, joiningdate)='2015'.

11. Write a SQL Query to get employee details from employee table whose joining month is     January.

Ans: Select * from employee where datepart (mm, joiningdate)='1'.

12. Write a SQL Query to get employee details from employee table whose joining date between "2015/01/01" and "2015/12/01".

Ans: Select * from employee where joiningdate between '2015/01/01' and '2015/12/01'.

13. Write a SQL Query to get how many employee exist in employee.

Ans: Select count(*) from employee.

14. Write an SQL Query to check whether date passed to Query is date of given format or not.

Ans: SQL has IsDate() function which is used to check passed value is date or not of specified format ,it returns 1(true) or 0(false) accordingly. Remember ISDATE() is a MSSQL function and it may     not work on Oracle, MySQL or any other database but there would be something similar.

SELECT  ISDATE('1/08/13') AS "MM/DD/YY";