Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL query finding dates between with an If else statement

Posted on 2013-06-06
10
613 Views
Last Modified: 2013-06-10
Hello,

I have a query that needs to select data using BETWEEN start-date AND end-date, as well as an if/else statement in it. I am not sure how to write it.

Select <field> from <table> where orderdate between <start-date> and IF(end-date <> 
cast('1901/01/01' as datetime) then use todays-date ELSE use end-date)

Any help with the if/else part of the above query would be greatly appreciated
0
Comment
Question by:dwknight
  • 4
  • 4
  • 2
10 Comments
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39227959
Try
select *
from tab1
where date1 between cast('2013-5-1' as datetime) and cast('2013-5-15' as datetime);
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39227962
try this
select
<field>
from <table>
where orderdate between <start-date>
 and (case when <end-date> <> cast('1901/01/01' as datetime) then getdate() else <end-date> end)

Open in new window

0
 

Author Comment

by:dwknight
ID: 39227965
Thanks for your suggestion, but what I am looking for is an if statement where the second date is located in your above answer.

I have to apologise, the original question is written incorrectly.

What I am looking for using your example above as a base is:

select *
from tab1
where date1 between cast('2013-5-1' as datetime) and IF <end-date> = cast('1901/01/01' as datetime {use the current date} ELSE use cast(<end-date> as datetime);
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 39227969
sorry, maybe it should be this:
select
<field>
from <table>
where orderdate between <start-date>
 and (case when <end-date> = cast('1901/01/01' as datetime) then getdate() else <end-date> end

Open in new window

I'm assuming 1901-01-01 is the default value in <end-date> which indicates it has not ended, so therefore use getdate(). If it has ended then use the <end-date>.

1901-01-01 is not the base date by the way, are you sure its not 1900-01-01?
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39227973
Try
select *
from tab1
where date1 between cast('2013-5-1' as datetime) and case when convert(varchar,[end-date],112)='19010101' then getdate() else [end-date] end;
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39227974
just one other point, could <end-date> be NULL?
select
<field>
from <table>
where orderdate between <start-date>
 and (case when <end-date> = cast('1901/01/01' as datetime) OR <end-date> IS NULL then getdate() else <end-date> end)
 

Open in new window

NB my previous suggestion forgot a concluding ) at the end of the last line
0
 

Author Comment

by:dwknight
ID: 39228035
Thanks for the suggestion, alas, no joy! The query will retrieve all records - ignoring the end date selected using the if/else statement.

If I modify the query and use a specific end date (ie '2010/02/10') the query successfully returns the correct subset within the specified date range.
0
 

Author Comment

by:dwknight
ID: 39228044
Sorry about the last comment, I had not refreshed before adding the comment.

The default save date, if there is no end date is 01/01/1901.

Please ignore my last post as it related to the first suggestion. I will have to get back and implement the additional suggestions at a later stage.

Again, many thanks for your assistance.
0
 

Author Closing Comment

by:dwknight
ID: 39236667
Worked at Treat!  Many thanks for your asisstance!
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39236673
v.happy you have a solution - & thanks. Cheers, Paul
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question