Converting varchar to Int for date difference

Question

Post reply

Converting varchar to Int for date difference

protocoder

Old Hand

Points: 374
More actions
April 4, 2022 at 11:40 am
Go to Answer
#4010455
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
```
- This topic was modified 3 years, 3 months ago by protocoder.
Johan Bijnens

SSC Guru

Points: 135537
More actions
April 4, 2022 at 1:51 pm
Answer
#4010520
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

Viewing 5 posts - 1 through 5 (of 5 total)

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

Phil Parkin SSC Guru Points: 247172 More actions · Answer 1

--deleted

This reply was modified 3 years, 3 months ago by Phil Parkin.

ScottPletcher SSC Guru Points: 101192 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

protocoder Old Hand Points: 374 More actions · Answer 3

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.

This reply was modified 3 years, 3 months ago by protocoder.

Converting varchar to Int for date difference

Cookies on SQLServerCentral