How do I find the first Tuesday of a month?! (SQL)

Question

I’m performing the following exercise:

The tables are as follows: :

I already have the following code:

SELECT NOME
FROM CLIENTES C,VENDAS V, LIVROS L
WHERE V.CODIGO_CLIENTE=C.CODIGO_CLIENTE AND L.CODIGO_LIVRO=V.CODIGO_LIVRO AND TO_NUMBER(TO_CHAR(DATA_VENDA,'HH24'))<17 AND GENERO='Informática'

But I don’t see any way to get the first Tuesday of the month, can anyone explain to me the missing part of the code?!

Answer 1

Try using Oracle date functions for example DAYNAME() if you compare DAYNAME([sua_data]) = 'TUESDAY' will return only Tuesday. Then limit using LIMIT 1

Answer 2

I made this solution to Sql Server because it is the bank that I have installed here at work, but I think you can convert to PL Sql easily, only do not do because would not have to test.

declare @weekdays table(id int, name varchar(10))

insert into @weekdays values (1, 'Sunday')
insert into @weekdays values (2, 'Monday')
insert into @weekdays values (3, 'Tuesday')
insert into @weekdays values (4, 'Wednesday')
insert into @weekdays values (5, 'Thursday')
insert into @weekdays values (6, 'Friday')
insert into @weekdays values (7, 'Saturday')

declare @data date = getdate() //usei data de hoje como exemplo
declare @inimonth date = dateadd(day, (day(@data)-1) * -1, @data)
declare @weekidDayOne int = 0

select top 1 @weekidDayOne = id from @weekdays where name = datename(dw, @inimonth)

select case when @weekidDayOne < 3 then dateadd(day, (3 - @weekidDayOne), @inimonth)
            when @weekidDayOne > 3 then dateadd(day, (7 - (@weekidDayOne - 3)), @inimonth)
            else @inimonth
        end

I used a variable Table because I needed the numbers to calculate, if you want to do it differently, maybe it’ll be better.

Version without variables, using only query

select case when weekdays.weekIniMonth < 3 then dateadd(day, (3 - weekdays.weekIniMonth), weekdays.inimonth)
            when weekdays.weekIniMonth > 3 then dateadd(day, (7 - (weekdays.weekIniMonth - 3)), weekdays.inimonth)
            else weekdays.inimonth
        end firstTueday, weekdays.*
from (
select case when datename(dw, datas.inimonth) = 'Sunday' then 1
            when datename(dw, datas.inimonth) = 'Monday' then 2
            when datename(dw, datas.inimonth) = 'Tuesday' then 3
            when datename(dw, datas.inimonth) = 'Wednesday' then 4
            when datename(dw, datas.inimonth) = 'Thursday' then 5
            when datename(dw, datas.inimonth) = 'Friday' then 6
            when datename(dw, datas.inimonth) = 'Saturday' then 7 end weekIniMonth, datas.*
from (select dateadd(day, (day('2017-07-14')-1) * -1, '2017-07-14') iniMonth) datas) weekdays

Answer 3

Thanks for the answers, I have found the final resolution of my problem.

SELECT NOME
FROM CLIENTES C,VENDAS V, LIVROS L
WHERE V.CODIGO_CLIENTE=C.CODIGO_CLIENTE AND L.CODIGO_LIVRO=V.CODIGO_LIVRO 
AND TO_NUMBER(TO_CHAR(DATA_VENDA,'HH24'))<17 AND GENERO='Informática' 
AND TO_CHAR(DATA_VENDA,'YYYY.MM.DD')=TO_CHAR(NEXT_DAY(TRUNC(DATA_VENDA,'Day')-1,'TERÇA-FEIRA'),'YYYY.MM.DD');