Click here to Skip to main content
15,179,246 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi All,

I am wondering for one solution since from 2 hours.But I didn't find any solution.So , finally i came here.

Question: How to check the Datetime between twpDatetime columns

For ex:

Colummn1 Column2 Price
01/05/2019 31/06/2019 50
01/07/2019 31/09/2019 49
01/10/2019 31/12/2019 48
01/01/2020 31/01/2020 45

Now, How can i send Price based on Dates?.

If today date is 01/06/2019 then we will send the price as 50 or Today's Date is 25/01/2020 then we will send 45 like that.

Can any one suggest on this Query.

Thanks in Advanced,
Pavan

What I have tried:

Getting price between the Dates
Posted
Updated 25-Nov-20 5:54am
Comments
Gerry Schmitz 25-Nov-20 10:32am
   
You also need a "default" for when it's not "dollar daze".

SQL
SELECT ... FROM MyTable WHERE @MySuppliedDate BETWEEN Column1 AND Column2

But ... there are only 30 days in September, or June ... which means that either you typed your example wrong, or you are storing dates in VARCHAR or NVARCHAR fields. That's a very bad idea: whenever you want to use them - such as now - you have to convert them to a DATE or DATETIME value in order to compare them properly. And if you have invalid date in tehre the conversion will fail and so will your query ...
   
v2
Comments
CHill60 25-Nov-20 10:49am
   
Beat me to it
Further to Solution 1 - learn to use unambiguous date formats so you don't get messed up with international differences. For example 01/05/2020 is "1st May 2020" in the UK, but "5th February 2020" in the USA.
There is quite a strong statement on this here .. SQL Server unambiguous date strings - Matt Randle[^]
and a somewhat better discourse on why, here ..SQL Server – Understanding Datetime column – Part II – Madhivanan's sql blog[^]

E.g.
SQL
declare @prices table (datefrom date, dateto date, price decimal(15,2))
insert into @prices (datefrom, dateto, price) values
('2019-05-01', '2019-06-30', 50.00),
('2019-07-01', '2019-09-30', 49.00),
('2019-10-01', '2019-12-31', 48.00),
('2020-01-01', '2020-01-31', 45.00)

declare @testdate date = '2019-11-25'
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900