Checking date within a range in SQL

Navigation
1

I need to check in my SELECT whether the e.DataTrm field is between 30 and 90 days from today's date. How to make condition in CASE? 

SELECT 
         e.NmrCnt AS [Contrato]
        ,e.NmrSerie AS [Serie]
        ,e.DataAfr AS [Data Aferição]
        ,e.DataTrm AS [Data Término]
        ,CASE 
            WHEN e.DataTrm < GETDATE() THEN 'Vencida'
            WHEN CONDIÇÃO THEN 'Vence em 30 à 90 dias'
        END AS [Status]
    FROM
    tbl_Eqp e
    
asked by anonymous 21.09.2016 / 20:49

2 answers

2

You can use the DATEDIFF that follows the difference of the searched date according to the first parameter, in your case in days (DAY). 

CASE WHEN DATEDIFF(DAY, e.DataTrm, GETDATE()) BETWEEN 30 AND 90 
THEN 'Vencida a ' + CONVERT(VARCHAR, DATEDIFF(DAY, e.DataTrm,GETDATE())) +'dias' ELSE '' END AS [Status]
    
21.09.2016 / 21:03
1

In this Fiddle you see working

Layout: 

create table teste (
NmrCnt varchar(200),
DataTrm DATETIME DEFAULT NULL);

insert into teste VALUES (1, GETDATE());
insert into teste VALUES (2, (dateadd(dd, 30, GETDATE())));
insert into teste VALUES (3, (dateadd(dd, 31, GETDATE())));
insert into teste VALUES (4, (dateadd(dd, 90, GETDATE())));
insert into teste VALUES (5, (dateadd(dd, 91, GETDATE())));

SQL: 

select 
NmrCnt as Contato
,CASE 
            WHEN DataTrm < GETDATE() THEN 'Vencida'
            WHEN (DataTrm < (dateadd(dd, 90, GETDATE())))
              and (DataTrm > (dateadd(dd, 30, GETDATE()))
                                   ) THEN 'Vence em 30 à 90 dias'
            ELSE 'nada'
      END AS [Status]
 from teste

I'm using SQLServer 2014 if you do not mind.

    
21.09.2016 / 21:06
Entity FrameWork (ORM) and DropDownList Html Conversion from byte array to string when compiling revealing string in compiled code in C