Interview Questions on "SQL DATE Functions"
30. Get employee details from employee table whose joining year is “2013”
SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'YYYY')='2013'
SQL Queries in SQL Server, Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,103),7,4)='2013'
SQL Queries in MySQL, Select * from EMPLOYEE where year(joining_date)='2013'
SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'MM')='01' or Select * from EMPLOYEE where to_char(joining_date,'Mon')='Jan'
SQL Queries in SQL Server, Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,100),1,3)='Jan'
SQL Queries in MySQL, Select * from EMPLOYEE where month(joining_date)='01'
32. Get employee details from employee table who joined before January 1st 2013
SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE <to_date('01/01/2013','dd/mm/yyyy')
SQL Queries in SQL Server (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date <'01/01/2013'
SQL Queries in MySQL (Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date <'2013-01-01'
33. Get employee details from employee table who joined after January 31st
SQL Queries in Oracle, Select * from EMPLOYEE where JOINING_DATE >to_date('31/01/2013','dd/mm/yyyy')
SQL Queries in SQL Server and MySQL (Format - “MM/DD/YYYY”), Select * from EMPLOYEE where joining_date >'01/31/2013'
SQL Queries in MySQL (Format - “YYYY-DD-MM”), Select * from EMPLOYEE where joining_date >'2013-01-31'
35. Get Joining Date and Time from employee table
SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyy hh:mi:ss') from EMPLOYEE
SQL Queries in SQL Server, Select convert(varchar(19),joining_date,121) from EMPLOYEE
SQL Queries in MySQL, Select CONVERT(DATE_FORMAT(joining_date,'%Y-%m-%d-%H:%i:00'),DATETIME) from EMPLOYEE
36. Get Joining Date,Time including milliseconds from employee table
SQL Queries in Oracle, select to_char(JOINING_DATE,'dd/mm/yyyy HH:mi:ss.ff') from EMPLOYEE . Column Data Type should be “TimeStamp”
SQL Queries in SQL Server, select convert(varchar,joining_date,121) from EMPLOYEE
SQL Queries in MySQL, Select MICROSECOND(joining_date) from EMPLOYEE
37. Get difference between JOINING_DATE and INCENTIVE_DATE from employee and incentives table
Select FIRST_NAME,INCENTIVE_DATE - JOINING_DATE from employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID
38. Get database date
SQL Queries in Oracle, select sysdate from dual
SQL Queries in SQL Server, select getdate()
SQL Query in MySQL, select now()