Solved

SQL query finding dates between with an If else statement

Posted on 2013-06-06
10
609 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL server is using more virtual memory. 5 84
SQL Server stored proc 2 19
SQl Server 2012 r2 - Why temp table shrinks from large query 4 37
Query Syntax 17 34
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

772 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