Conversion failed when converting date and/or time from character string

Question

I have this SQL Server query which is returning Conversion failed when converting date and/or time from character string.

(
    @numberRecords int,
    @date varchar(12)
)

AS
SET ROWCOUNT @numberRecords
SELECT re.ID, re.RecentChangeTypeID, re.LogID, re.RevisionID
FROM RecentChanges re
    FULL OUTER JOIN Revisions rev
        ON re.RevisionID = rev.ID
    FULL OUTER JOIN Logs lg
        ON re.LogID = lg.ID
WHERE convert(datetime, rev.Timestamp) >= '@date' OR
      convert(datetime, lg.Timestamp) >= '@date';
RETURN

The date parameter returns a string value in the format "dd-MM-yyyy". What may be the cause?

why not store rev.Timestamp and lg.Timestamp as datetime columns? — KM.
– KM., Commented May 4, 2012 at 18:38
+1, @KM. - Nothing good ever comes from storing dates or numbers as strings — JNK
– JNK, Commented May 4, 2012 at 18:40
@JNK, sorting problems, conversion problems, and invalid data! — KM.
– KM., Commented May 4, 2012 at 18:41

JNK · Accepted Answer · 2012-05-04 18:37:18Z

4

Don't put your @date parameter inside single quotes - it's being interpreted as a string instead of as a parameter.

As written you are trying to make '@date' a datetime which won't parse.

That being said, you should probably make the input parameter a datetime in the first place.

answered May 4, 2012 at 18:37

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

KM. Over a year ago

+1, the code is comparing the table columns against the sting literal "@date". You need to compare the table columns against the variable @date. If you say MyColumn='abc' you are comparing your column against the constant string "abc". if you say MyColumn='@abc' you are still comparing you column against the a constant string "@abc". if you say MyColumn=@abc you are comparing your column against the value within the @abc variable.

KM. · Accepted Answer · 2012-05-04 18:53:49Z

1

remove the single quotes as @JNK has described to make your query work. However, for possibly much better performance (index usage) try this query (if you have indexes on the columns Revisions.Timestamp and Logs.Timestamp) :

SELECT 
    re.ID, re.RecentChangeTypeID, re.LogID, re.RevisionID
FROM RecentChanges         re
    INNER JOIN Revisions   rev ON re.RevisionID = rev.ID
WHERE rev.Timestamp >= @date
UNION
SELECT 
    re.ID, re.RecentChangeTypeID, re.LogID, re.RevisionID
FROM RecentChanges    re
    INNER JOIN Logs   lg ON re.LogID = lg.ID
WHERE lg.Timestamp >= @date

answered May 4, 2012 at 18:53

KM.

104k34 gold badges183 silver badges214 bronze badges

Comments

EnnioG · Accepted Answer · 2012-05-04 18:34:04Z

0

it looks to me, at first glance, that you are converting to datetime, not from it. I am assuming rev.Timestamp is a datetime already, while your @date is a varchar(12)...so SQL Server is complaining that it can't compare a datetime to a string...

answered May 4, 2012 at 18:34

EnnioG

1

2 Comments

JNK Over a year ago

I'm not sure what this answer means...You're basically talking in circles.

EnnioG Over a year ago

no, i'm not. your convert statement converts a datetime to another datetime, takes the result and compares it to a varchar(12)

Collectives™ on Stack Overflow

Conversion failed when converting date and/or time from character string

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related