Avatar of Taras
Taras
Flag for Canada asked on

Date between does not work for some date range

I am testing my SQL Server script with date range and I have set up two variable Start and End date.

DECLARE @Start_Date DATETIME

DECLARE @End_Date   DATETIME

 

SET @Start_Date = DateAdd(dd,-15,GETDATE())

 

 

SET @End_Date = DateAdd(dd,-1,GETDATE())

 

In Where part of script have I am filtering records :

Ord.[ORDER_START_Date_TIME] Between Convert(Varchar,@Start_Date,111) And Convert(Varchar,@End_Date,111)

 

If I set up SET @Start_Date = DateAdd(dd,-15,GETDATE())

It is working and bringing result, if I put days all way up to 19 it is working and bringing records back, when setup start date like:

 SET @Start_Date = DateAdd(dd,-20,GETDATE())

 

I got error:

Msg 245, Level 16, State 1, Line 16

Conversion failed when converting the ****** value '******' to data type ******.

Any Idea what could be wrong?

SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Taras

8/22/2022 - Mon
Taras

ASKER
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.
ste5an

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

Open in new window

ASKER CERTIFIED SOLUTION
Scott Pletcher

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Taras

ASKER
HI ste5an I tried your suggestion got the same error on DateAdd(dd,-20,GETDATE())  and more,  19 is working!?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Scott Pletcher

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.
ste5an

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

Open 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;

Open in new window

To find the offending rows.
Taras

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

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))

ste5an

No cast needed here:

Capture.PNG
Taras

ASKER
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.

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ste5an

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

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

ste5an

I'm not.

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

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Taras

ASKER
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
......
Taras

ASKER
Order By Ord.ORDER_START_Date_Time Desc
ste5an

You need to look for NULL values.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Scott Pletcher

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?

Taras

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

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

Open in new window

Without any CAST() or CONVERT().

Post your concrete SQL statement and the exact error message.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Taras

ASKER
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.