Face error this in sql.

Rohan

Hello everyone I have face this issue in sql Conversion failed when converting date and/or time from character string. I have compare two date

UserClaims.ClaimType='RenewDate' AND UserClaims.ClaimValue < GETDATE()

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Muhammad Imran Ansari4y ago
In addition of below comments, Further you can check different date formats of SQL according to your data
https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/
Rohan4y ago
I have use this

SELECT *
FROM Users
JOIN UserClaims ON UserClaims.UserId=Users.Id
WHERE UserClaims.ClaimType='IsAutoRenewDate' AND (UserClaims.ClaimValue) > CONVERT(varchar,GETDATE(),127) AND Users.Id=UserClaims.UserId
It's working for me.
Sachin Singh4y ago
No this is wrong, it will never return any value
test this
SELECT *
FROM Users
JOIN UserClaims ON UserClaims.UserId=Users.Id
WHERE UserClaims.ClaimType='IsAutoRenewDate' AND Convert(datetime,UserClaims.ClaimValue) < CONVERT(datetime, GETDATE()) AND Users.Id=UserClaims.UserId
Rohan4y ago
I have use this

SELECT *
FROM Users
JOIN UserClaims ON UserClaims.UserId=Users.Id
WHERE UserClaims.ClaimType='IsAutoRenewDate' AND UserClaims.ClaimValue < CONVERT(varchar,GETDATE(),103) AND Users.Id=UserClaims.UserId
It's working but Record not found
Rohan4y ago
Sachin Sir Same Issue face

SELECT *
FROM Users
JOIN UserClaims ON UserClaims.UserId=Users.Id
WHERE UserClaims.ClaimType='RenewDate' AND CAST(UserClaims.ClaimValue as datetime) > Cast(GetDate() as datetime) AND Users.Id=UserClaims.UserId
Sachin Singh4y ago
it should be cast actually
UserClaims.ClaimType='RenewDate' AND Cast(UserClaims.ClaimValue as Datetime) <
Cast(GETDATE() as Datetime)
Rohan4y ago
Yes Sir Is the column stores datetime?

86 17 RenewDate 2022-06-16 05:09:13Z This Is Columns value.
Sachin Singh4y ago
Then why are you comparing with getdate()? Is the column stores datetime?
Rohan4y ago
Sir RenewDate is a string type .

I have use this query not working

UserClaims.ClaimType='RenewDate' AND Convert(UserClaims.ClaimValue as Datetime) <
Convert(GETDATE() as Datetime)
Sachin Singh4y ago
Claim value is never a datetime, Issue a select query to find the claim value. It will be a varchar. You need to use another column from claims table like createdOn Etc
if your claim value really contains datetime then use conversion
UserClaims.ClaimType='RenewDate' AND Convert(UserClaims.ClaimValue as Datetime) <
Convert(GETDATE() as Datetime)