Checking date within a range in SQL

Asked

Viewed 179 times

1

I need to check in my SELECT if the field e. Dating back to is between 30 and 90 days from today’s date. How to make the 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
sql dateinterval

2 answers

2


You can use the DATEDIFF that gives you the difference of the date searched 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]

1

In that Fiddle you see working

Scheme:

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 don’t mind.

Browser other questions tagged sql dateinterval

You are not signed in. Login or sign up in order to post.