Raviteja Gattu Raviteja Gattu - 4 months ago 16
SQL Question

Conversion failed when converting the varchar value '23/05/1994' to data type int

declare @DOB int
declare @age int
select @DOB=coalesce(@DOB+'23/05/1994','')
set @age=DATEDIFF(YEAR,@DOB,GETDATE())

select @age

(or)

select DATEDIFF(MONTH,'23/05/1994','23/06/1995')


Conversion failed when converting the varchar value
'23/05/1994'
to data type
int
.

i am getting this response when executing the above queries, help me out if it, Thankyou

Answer

Since you are using the dd/mm/yyyy format, instead of that use mm/dd/yyyy format. For your case you need to covert the date into mm/dd/yyyyby theCONVERT(DATETIME, 'date', 105)`.

select @DOB=coalesce(@DOB+'23/05/1994','')

the above query is invalid, hope you need to add the day/month to the date value, in this case you can use DATEADD

So the working code:

declare @DOB int = 2;
declare @age int;
DECLARE @DateOfBirth DATETIME;
--select @DOB=coalesce(@DOB + '23/05/1994','')
SELECT @DateOfBirth=DATEADD(DAY, @DOB, CONVERT( DATETIME, '23/05/1994', 105));
set @age=DATEDIFF(YEAR,@DateOfBirth,GETDATE())
select @age;

--(or)

select DATEDIFF(MONTH, CONVERT(DATETIME,'23/05/1994', 105), CONVERT(DATETIME,'23/06/1995', 105));