Top 100 sql server queries interview questions


SQL Server Advanced Questions - 'DateTime'


25) Get first name, admission year, admission month and admission date from tblStudent table.
1
2
3
4
select SUBSTRING (convert(varchar,admission_date,103),7,4) as Year,
SUBSTRING (convert(varchar,admission_date,100),1,3) as Month, 
SUBSTRING (convert(varchar,admission_date,100),5,2) as Date
from tblStudent
26) Get student details from tblStudent table whose admission year is "2015".
1
Select * from tblStudent where SUBSTRING(convert(varchar,Admission_date,103),7,4)='2015'
27) Get student details from tblStudent table whose admission date is after January 31st.
1
Select * from tblStudent where Admission_date >'01/31/2016'
28) Get student details from tblStudent table whose admission date is before January 31st.
1
Select * from tblStudent where Admission_date <'01/31/2016'
29) Get student details from tblStudent table whose admission month is "January".
1
Select * from tblStudent where SUBSTRING(convert(varchar,Admission_date,100),1,3)='Jan'
30) Get admission date and time from tblStudent table.
1
Select convert(varchar(19), admission_date,121) from tblStudent
31) Get database date.
1
select getdate()
32) Get UTC date.
1
select GETUTCDATE()
33) Get only month part of admission date from tblStudent.
1
select DATEPART(MONTH, admission_date) from tblStudent
34) Get only year part of admission date from tblStudent.
1
select DATEPART(YEAR, admission_date) from tblStudent
35) Get all student details from tblStudent table whose admission date between '2015-01-01' and '2016-01-01'.
1
select * from tblStudent where admission_date between '2015-01-01' and '2016-01-01'
36) Get the first name, last name, current date, admission date and difference between current date and admission date in days.
1
2
select FirstName, LastName, GETDATE() as 'Current_date', Admission_date,
DATEDIFF(DD, Admission_date, GETDATE()) As days from tblStudent
37) Get the first name, last name, current date, admission date and difference between current date and admission date in month.
1
2
select FirstName, LastName, GETDATE() as 'Current_date', Admission_date,
DATEDIFF(MM, Admission_date, GETDATE()) As months from tblStudent
38) Get the first name, last name, current date, admission date and difference between current date and admission date in year.
1
2
select FirstName, LastName, GETDATE() as 'Current_date', Admission_date,
DATEDIFF(YYYY, Admission_date, GETDATE()) As years from tblStudent
39) Show "AdmissionDate" in "dd mmm yyyy" format, ex- "06 May 2016".
1
select CONVERT(varchar(30), admission_date, 106) from tblStudent
40) Show "AdmissionDate" in "yyyy/mm/dd" format, ex- "2016/05/06".
1
select CONVERT(varchar(30), admission_date, 111) from tblStudent
41) Show only time part of the "AdmissionDate" from tblStudent.
1
Select CONVERT(varchar(20), admission_date, 108) from tblStudent
42) Select no of students get admission with respect to year and month from tblStudent table.
1
2
3
select datepart (YYYY,Admission_date) Admission_Year, datepart (MM,Admission_date)
Admission_Month,count(*) Total_Student from tblStudent group by datepart(YYYY,Admission_date),
datepart(MM,Admission_date)
« 1 2 3 4 3 4 »
 

Responses




Popular Posts

Facebook

Founded in 2016, Tech Study passionately delivers stylish and dynamic innovative information to programmer and technology lovers. http://www.techstudy.org/.