Hi Experts,
Constraints: I do not have create permissions and I am learning
This is my code
Basically testing to fix the T-SQL putting junk 1900 dates and if valid then find the difference between FADate and TDate
Unfortunately, even after trying to convert the varchar to Int using convert(int, a.FAdate) I get conversion error.
The other way is to first find the difference using datediff and verify which date is 1900 to put a string as I need to collect which record has valid, invalid dates but i need to do check for zero difference or Huge difference.
Any smart way to resolve this please ?
,IIF(
(CONVERT(varchar(10),a.FADate,105)) > '01-01-1990' ,
IIF(
(CONVERT(varchar(10),a.TDate,105)) > '01-01-1990' , DATEDIFF(d,CONVERT(INT,a.FADate), CONVERT(INT,a.TDate)),
'NoTDate'),
'NoFADate'
) AS DateDiffIfVerified
Don't convert a columns data type if it is not needed
If conversion is needed, convert to the correct data type.
Declare @tb table ( FADate date not null, TDate date not null )
Insert into @tb values ( '2021-01-01', '2022-01-01' ),( '1899-01-01', '2022-01-01' )
Declare @DtRef date = '1900-01-01'
SELECT IIF( a.FADate > @DtRef,
-- true
IIF(a.TDate > @DtRef,
--true
-- converting to varchar because of the use of text 'NoTdate' and 'NoFADate'
convert(varchar(25), DATEDIFF(d, a.FADate, a.TDate) )
, 'NoTDate'),
-- false
'NoFADate') AS DateDiffIfVerified
from @tb a;
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
April 4, 2022 at 1:51 pm
--deleted
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 4, 2022 at 2:22 pm
Btw, I think it would be better if you used a separate column for the string message to keep the diff column as only numeric, but that's just IMO.
SELECT
CASE WHEN a.FADate <= '19900101' THEN 'NoFADate'
WHEN a.TDate <= '19900101' THEN 'NoTDate'
ELSE CAST(DATEDIFF(DAY, a.FADate, a.TDate) AS varchar(8)) END AS DateDiffIfVerified
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 5, 2022 at 12:40 am
Thank you @JohanBijnens, Great help, I will test this smart stuff.
@scottpletcher, wow for simple and great idea. I will remember this easy technique. To both of you thanks a ton.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply