DateDiff - Data Type conversion Issues

Question

Post reply

DateDiff - Data Type conversion Issues

protocoder

Old Hand

Points: 374

May 16, 2022 at 2:06 am

Go to Answer

#4033442

Hi Experts

Not able to do DATEDIFF between two dates, but it works when these dates are part of ( a.ClosingDate, calc.maxDate ) CTE

and in select I use DATEDIFF , My result is taking forever so I was trying to get rid of CTE so I encounter this issue. Any help please.

I attempted the following 3 ways as below

1. 
DATEDIFF(day,a.ClosingDate, calc.maxDate) as DiffDate
"Error: Conversion failed when converting date and/or time from character string. "
2.
DATEDIFF(day, CONVERT(DATETIME, a.ClosingDate), CONVERT(DATETIME,calc.maxDate))+ 1 AS DiffDate
" Error 
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."
3.
DATEDIFF(day, CONVERT(varchar(24),CONVERT(DATETIME,a.ClosingDate),105),CONVERT(varchar(24),CONVERT(DATETIME,calc.maxDate),105)) AS DiffDate

"Conversion failed when converting date and/or time from character string."

protocoder

Old Hand

Points: 374
More actions
May 16, 2022 at 9:30 am
Answer
#4033587

Hey Sorry,
I got this sorted Thank you.
CASE
WHEN
WHEN d.CancelDate ='1900-01-01 00:00:00.000' AND d.ClosingDate = '1900-01-01 00:00:00.000' THEN '' --'NO Valid Closing Date'
ELSE DATEDIFF(day, d.ClosingDate, d.CancelDate)+1
END AS DateDiff
and it worked.
May 17, 2022 at 6:05 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ktflash Default port Points: 1400 More actions · Answer 1

You could check the rows that cause the conversion error.

The function returns null if the row fails to cast, so you only get the rows that fail to convert.

select ClosingDate
from table
where try_cast(ClosingDate as date) is null

I want to be the very best
Like no one ever was

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

protocoder Old Hand Points: 374 More actions · Answer 2

@ktflash

Thank you, Yes earlier i had a filter earlier that eliminated nulls but i have so many conditions to prepare in one report, i have to Where filter and use flags in window functions. Assuming dates have invalid, null. is there a way to handle this

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 3

Don't you just love storing datetime/date/time information in (var)char columns.?

Now you're stuck with the downsides of this design option.

Don't let the engine guess what format the dates are stored in, use CONVERT to point to the correct format!

Of course, the correct fix would be to have these columns in the correct data type !

Select @ClosingDate = '13-01-2022', @maxDate = '2022-05-16'
Select DATEDIFF(day,convert(date,@ClosingDate,105), convert(date,@maxDate,23)) as DiffDate

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

protocoder Old Hand Points: 374 More actions · Answer 4

Hello @Johan

Sir, I remember your advice you gave earlier. I am just another BA with no create access too. This is the existing data I am migrating.

But thank you to all. I am learning something. 🙂

Jeffrey Williams SSC Guru Points: 90252 More actions · Answer 5

protocoder wrote:

Hey Sorry,
I got this sorted Thank you.
CASE WHEN WHEN d.CancelDate ='1900-01-01 00:00:00.000' AND d.ClosingDate = '1900-01-01 00:00:00.000' THEN '' --'NO Valid Closing Date' ELSE DATEDIFF(day, d.ClosingDate, d.CancelDate)+1 END AS DateDiff
and it worked.

This actually isn't working the way you think it is...

DATEDIFF returns a numeric value - the number of days difference. The first part of the case expression is returning an empty string - which is then implicitly converted to an integer and will be returned as a 0.

Also, this solution doesn't appear to be related to your earlier question. You earlier question was concerning calc.MaxDate - and I am guessing that value was coming from that CTE. And finally, the datetime '1900-01-01 00:00:00.000' is implicitly converted to a datetime - so I doubt those values are the ones causing you problems.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

protocoder Old Hand Points: 374 More actions · Answer 6

@Jeffrey,

You are absolutely spot on with not relevant to max.days. Max.days is actually courtesy from DesNorton

https://www.sqlservercentral.com/forums/topic/question-on-last-valid-date-in-the-system

In this system I have used that function for multiple dates and as in this report, I just need max of two dates and diff with other date, I replaced that with

CASE statement as above and it seems working, though not tested more than 1 or two samples. I will once I am done with my stuff, will do full testing and probably replace with the solution from Johan above.

Note: That max date is not in the calculation, I used that for testing.

DateDiff

This reply was modified 1 year, 11 months ago by protocoder.