Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 650
  • Last Modified:

SQL query finding dates between with an If else statement

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
dwknight
Asked:
dwknight
  • 4
  • 4
  • 2
1 Solution
 
HuaMinChenBusiness AnalystCommented:
Try
select *
from tab1
where date1 between cast('2013-5-1' as datetime) and cast('2013-5-15' as datetime);
0
 
PortletPaulCommented:
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
 
dwknightAuthor Commented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
PortletPaulCommented:
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
 
HuaMinChenBusiness AnalystCommented:
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
 
PortletPaulCommented:
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
 
dwknightAuthor Commented:
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
 
dwknightAuthor Commented:
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
 
dwknightAuthor Commented:
Worked at Treat!  Many thanks for your asisstance!
0
 
PortletPaulCommented:
v.happy you have a solution - & thanks. Cheers, Paul
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now