How to check date between two date time coulmns in SQL

Question

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 25-Nov-20 4:00am

Member 12057131

Updated 25-Nov-20 4:54am

Add a Solution

Comments

[no name] 25-Nov-20 10:32am

You also need a "default" for when it's not "dollar daze".

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2020-11-25T04:45:00

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 ...

CHill60 · Answer 2 · 2020-11-25T04:54:00

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'

How to check date between two date time coulmns in SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us