Solved

SQL query finding dates between with an If else statement

Posted on 2013-06-06
10
631 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 11

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 49

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 49

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 11

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 49

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 49

Expert Comment

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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

617 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