Date between does not work for some date range

I As Ord.[ORDER_START_Date_TIME]  is datetime field type I corrected my filter:

 Ord.[ORDER_START_Date_Time] Between @Start_Date And @End_Date and got the same error.

The question is: Why casting at all?

Store your ORDER_START_Date_TIME as DATETIME or DATE and your save.

Just be aware that you need to consider the time portion, thus you may need to use DATE.

DECLARE @Start_Date DATE = DateAdd(dd,-15,GETDATE());
DECLARE @End_Date   DATE = DateAdd(dd,-1,GETDATE());

Ord.[ORDER_START_Date_TIME] BETWEEN @Start_Date AND @End_Date

Select allOpen in new window

HI ste5an I tried your suggestion got the same error on DateAdd(dd,-20,GETDATE())  and more,  19 is working!?

For example, if then date is yesterday, and you used BETWEEN, then you would only get rows from yesterday where the time was exactly midnight, since the default time is midnight (00:00:00.000).  You'd miss yesterday at 9AM, for example.

By using >= and <. you get all times for yesterday.

The problem is your source. The data type of this column must be DATE or DATETIME.

E.g.

DECLARE @Start_Date DATE = DateAdd(dd,-15,GETDATE());
DECLARE @End_Date   DATE = DateAdd(dd,-1,GETDATE());

CAST(Ord.[ORDER_START_Date_TIME] AS DATE) BETWEEN @Start_Date AND @End_Date

Select allOpen in new window

If this still raises your error, then you have stored a string which is not a date or date/time.

Use TRY_CAST():

SELECT TRY_CAST(Ord.[ORDER_START_Date_TIME] AS DATE) 
FROM yourTable;

Select allOpen in new window

To find the offending rows.

I tried this:Ord.[ORDER_START_Date_TIME] >= @Start_Date And
Ord.[ORDER_START_Date_TIME] < DATEADD(DAY, 1, @End_Date)  the same error.

DECLARE @Start_Date DATETIME
DECLARE @End_Date   DATETIME
 
SET @Start_Date = DateAdd(dd,-15,CAST(GETDATE() AS date))
SET @End_Date = DateAdd(dd,-1,CAST(GETDATE() AS date))

No cast needed here:

Thank you both, in my select statement in list of fields I converted Order_Start_Date_Time to : CONVERT(VARCHAR,ord.[ORDER_START_Date_Time],111) and that trigger issue. I corrected that and after that it worked as you suggested.

This is a cast in the wrong place. Cause now you're doing a string comparision.

I am surprised again it is not working...??

I'm not.

SELECT TRY_CAST(Ord.[ORDER_START_Date_TIME] AS DATE) 
FROM yourTable;

Select allOpen in new window

when I run :
SELECT
TRY_CAST(Ord.[ORDER_START_Date_Time as DATE)
From
 MyTable ord
  Order By Ord.ORDER_START_DT_TM Desc
I got :
2021-11-23
2021-11-23
2021-11-23
2021-11-23
2021-11-23
2021-11-22
2021-11-19
2021-11-19
2021-11-19
2021-11-19
2021-11-17
2021-11-17
2021-11-17
2021-11-16
2021-11-16
2021-11-15
2021-11-15
2021-11-10
2021-11-10
......

Order By Ord.ORDER_START_Date_Time Desc

You need to look for NULL values.

I tried this:Ord.[ORDER_START_Date_TIME] >= @Start_Date And
Ord.[ORDER_START_Date_TIME] < DATEADD(DAY, 1, @End_Date)  the same error.

That's not possible if the Ord.[ORDER_START_Date_TIME] column is a date or datetime data type.

What is the data type of Ord.[ORDER_START_Date_TIME]?  If it's char/varchar (yuck!), what specific format is it stored in?

it is Datetime:2021-11-23 14:52:13.707

Then it's as I initially wrote:

DECLARE @Start_Date DATE = DateAdd(dd,-15,GETDATE());
DECLARE @End_Date   DATE = DateAdd(dd,-1,GETDATE());

Ord.[ORDER_START_Date_TIME] BETWEEN @Start_Date AND @End_Date

Select allOpen in new window

Without any CAST() or CONVERT().

Post your concrete SQL statement and the exact error message.

Now it is working. Conversion error was triggered in another part of script Where I calculated Day of Stay with DATEDIFF function. I used it in CASE WHEN.....statement to count Length of Stay and at the end of  it  in "Else" part I had 'N/A' instead 0.
 So I apologize as  your suggestion was good and appropriate.