Face error this in sql.

Question

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()

Muhammad Imran Ansari · Answer

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/

Rohan · Answer

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 Singh · Answer

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

Rohan · Answer

I have use this SELECT * FROM Users JOIN UserClaims ON UserClaims.UserId=Users.Id WHERE UserClaims.ClaimType='IsAutoRenewDate' AND UserClaims.ClaimValue It's working but Record not found

Rohan · Answer

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 Singh · Answer

it should be cast actually UserClaims.ClaimType= 'RenewDate' AND Cast(UserClaims.ClaimValue as Datetime) Cast(GETDATE() as Datetime)

Rohan · Answer

Yes Sir Is the column stores datetime? 86 17 RenewDate 2022-06-16 05:09:13Z This Is Columns value.

Sachin Singh · Answer

Then why are you comparing with getdate()? Is the column stores datetime?

Rohan · Answer

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 Singh · Answer

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)

Face error this in sql.

Answers (10)