StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POFinding the latest date of 2 date columns in SQL
primarykey
Id
20427073
data
AcceptedAnswerId
20427207
AnswerCount
3
ClosedDate
CommentCount
3
CommunityOwnedDate
CreationDate
2013-12-06T15:02:43.513
FavoriteCount
0
LastActivityDate
2013-12-06T17:07:00.870
LastEditDate
2013-12-06T17:07:00.870
LastEditorUserId
50198
OwnerUserId
50198
ParentId
0
PostTypeId
1
Score
-1
ViewCount
68
LastEditorDisplayName
text
Body
I'm having trouble finding a solution to the following SQL query: I have a table that contains 2 date columns: <code>PurchaseDate</code> and <code>RefundDate</code> I have a <code>StartDate</code> and <code>EndDate</code> that defines the date range for the query. I want to restrict the query by either the <code>PurchaseDate</code> OR the <code>RefundDate</code> whichever is earliest. Also either date can be null. So if PurchaseDate is 18.Nov.2013 and RefundDate is 03.Dec.2013 then I want to use <code>PurchaseDate</code> in the <code>where</code> clause to restrict the result set within the <code>startDate</code> and <code>EndDate</code> Another example, is if PurchaseDate is 01.Aug.2013 and RefundDate is 19.May.2013 then I would want to use RefundDate in the where clause. This is what I've got so far but it's not correct, I need to select whichever is the earliest of the 2 dates to restrict the where clause: <pre><code>SELECT OrderID, AddressID, PurchaseDate, RefundDate FROM Orders WHERE (PurchaseDate IS NOT NULL AND PurchaseDate >= @queryStartDate AND PurchaseDate <= @queryEndDate) OR (RefundDate IS NOT NULL AND RefundDate >= @queryStartDate AND RefundDate <= @queryEndDate) </code></pre> I thought about using <code>COALESCE</code> but this won't work as it will just pick the first date that is non-null rather than the earliest. I think I'm going to have to do two queries and union them but it feels like there is a better way and I can do it with one query and some clever magic in the where clause....
Tags
<sql><date><sql-server-2012>
Title
Finding the latest date of 2 date columns in SQL
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USChris B
UserOwnerUserId
1. USChris B
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POFinding the latest date of 2 date columns in SQL
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTDownMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.