sql compare dates code example

Example 1: compare date in sql

WHERE DATEDIFF(my_date,'2008-11-20') >=0;
WHERE my_date >= '2008-11-20';
WHERE to_date(my_date, 'YYYY-MM-DD') >= '2008-11-20';
WHERE to_date(my_date, 'YYYY-MM-DD') >= to_date('2008-11-20', 'YYYY-MM-DD');

Example 2: sql datediff hours and minutes

Small change like this can be done

  SELECT  EmplID
        , EmplName
        , InTime
        , [TimeOut]
        , [DateVisited]
        , CASE WHEN minpart=0 
        THEN CAST(hourpart as nvarchar(200))+':00' 
        ELSE CAST((hourpart-1) as nvarchar(200))+':'+ CAST(minpart as nvarchar(200))END as 'total time'
        FROM 
        (
        SELECT   EmplID, EmplName, InTime, [TimeOut], [DateVisited],
        DATEDIFF(Hour,InTime, [TimeOut]) as hourpart, 
        DATEDIFF(minute,InTime, [TimeOut])%60 as minpart  
        from times) source

Example 3: sql compare data from two tables

select * from tableA
minus
select * from tableB

Example 4: how to compare in sql

(Between) operator same as  ">= <="
For example: 
Select * From Employees Where salary Between 4000 AND 6000;

(NOT) operator excluding given
For example:
Select last_name, job_id From Employees
Where "Not" job_id = 'ABC';


(IN) operator in sql like "OR" operator
For example: 
Select * From employees
Where department_id "IN" (60,90); 


(Like) Operator for partial searches using wildcard '%' and '_'
For Example:
Select * From Employees
Where last_name LIKE '_a%';


(Top N results)
Select * From Employees Where ROWNUM <=5;


(NVL) replaces NULL values with same type default
value provided.
For Example = 
Select NVL(commission_percentage, 0)
From Employees;

Example 5: SQL server datetime compare

Pressupondo uma tabela utilizador u.

year(u.DataInicio) --> Retorna o ano
month() --> Retorna o mês
day() --> dia

Exemplo:
u.Ativo = 1 AND year(u.DataInicio)<= 2018