Solved

SQL query finding dates between with an If else statement

Posted on 2013-06-06
10
619 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
[X]
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
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2016 Integration Service connecting to 2012 3 102
SQL- GROUP BY 4 53
Split string into 3 separate fields 5 36
Enabling flash installation using GPO 2 55
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 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