Date Range is not accepted after using CONVERT function

Question

Hello, Newbee here
I had an issue with converting a date filed containing date & time to just Date. With help that was resolved. But after getting the date converted I could not get the date range to work.

This code is after adding in the CONVERT function and I have not included the date function. The second piece of code is my original, ie before converting the date. The date range in that code does work.

SELECT CONVERT (Date, [Order].CompletedDate) As DateOnly
,[Order].OrderTotal
,[Order].Discount
,OrderType.Name
FROM
OrderType
INNER JOIN [Order]
ON OrderType.OrderTypeId = [Order].OrderTypeId

Original Query without converting the CompletedDate field to DateOnly

SELECT
[Order].CompletedDate
,OrderType.Name
,[Order].OrderTotal
,[Order].Discount
FROM
OrderType
INNER JOIN [Order]
ON OrderType.OrderTypeId = [Order].OrderTypeId
WHERE
[Order].CompletedDate >= @CompletedDate
AND [Order].CompletedDate <= @CompletedDate2

Appreciate any help in how to specify the range date
Cheers
Bruce

Answer 1

Hi @Bruce Minchinton ,

Actually the convert of [Order].CompletedDate would not affect your where part and result. Your query could be working as before.

But we recommend you to use below query and you could check whether it is working.

 SELECT  
CONVERT (Date, [Order].CompletedDate) As DateOnly  
,OrderType.Name  
,[Order].OrderTotal  
,[Order].Discount  
FROM  
OrderType  
INNER JOIN [Order]  
ON OrderType.OrderTypeId = [Order].OrderTypeId  
WHERE  
[Order].CompletedDate >= @CompletedDate  
AND [Order].CompletedDate < DATEADD(DAY, 1, @CompletedDate2)  

Above assumes @CompletedDate and @CompletedDate2 are always passed as dates with no time.

If there is time involved (e.g. the app might pass now()) then you can round off using:

WHERE [Order].CompletedDate  >= CONVERT(DATE, @CompletedDate)  
  AND [Order].CompletedDate <  CONVERT(DATE, DATEADD(DAY, 1, @CompletedDate2));  

If above or your query is still not working, please provide some sample data and expected output, we could check further.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

The CONVERT to date effects only the returned result set, in the WHERE clause you can use the date filter as before.