Range set by SQL user

Question

Range set by SQL user

Navigation

#1 by (0 votes)

0

In sql, is there any way to define the range of a query according to the user's past?

Example

Number of days the user enters is 7, and set as the registration date 07/01/2018, the bank return the registration made on 01/01/2018 to 01/13/2018,?

sql date range

asked by anonymous 10.02.2018 / 01:24

1 answer

Site opens but does not load the files cs, js, png etc. JQuery 1.11.3 - Code does not work inside function loaded in $ (document) .ready only if inserted directly

score 0 · Accepted Answer

Yes, there are several ways! As you've attached the SQL tag, deduce that you are using T-SQL to write your queries.

So by analyzing the question with the proposed example I got to ...

SELECT nomeDaColuna(s)
FROM nomeDaTabela
WHERE ColunaQueContemData 
BETWEEN DataInicial AND CAST(DATEADD(day, 7, DataInicial) AS DATE)
GO

I even made a point to elaborate an example similar to yours to show that it works:

Script Used:

CREATE TABLE [dbo].[Vendas](
    [ID] INT IDENTITY PRIMARY KEY NOT NULL,
    [Data] DATE DEFAULT GETDATE(),
    [Valor] MONEY
)
GO


INSERT INTO [dbo].[Vendas]([Valor]) VALUES(12.55)
GO
INSERT INTO [dbo].[Vendas]([Data], [Valor]) VALUES(DATEADD(day, 2, GETDATE()), 15.99)
GO
INSERT INTO [dbo].[Vendas]([Data], [Valor]) VALUES(DATEADD(day, 4, GETDATE()), 21.15)
GO
INSERT INTO [dbo].[Vendas]([Data], [Valor]) VALUES(DATEADD(day, 6, GETDATE()), 85.00)
GO
INSERT INTO [dbo].[Vendas]([Data], [Valor]) VALUES(DATEADD(day, 7, GETDATE()), 17.99)
GO
INSERT INTO [dbo].[Vendas]([Data], [Valor]) VALUES(DATEADD(day, 8, GETDATE()), 20)
GO


SELECT * FROM [dbo].[Vendas]
GO


SELECT * FROM [dbo].[Vendas] 
WHERE [Data]
BETWEEN GETDATE()
AND CAST(DATEADD(day, 6, GETDATE()) AS DATE)
GO

I used the native T-SQL function:

GETDATE() - Returns the current date.
DATAADD(intervalo da data, quantidade, data que sofre operação) - Returns a date after undergoing an operation. The date range represents which date part will undergo the operation (day, month, year - for date). The quantity attribute represents the amount of day, month, or year that you want to ADD (Positive Numbers) and Subtract (Negative Numbers).
CAST() - used to convert DATETIME to DATE. I hope I have helped!