Getting error on subquery, "Conversion failed when converting datetime from character string"

  • Hi Experts!

    I am fairly new to SQL but not new to programming (10+ years exp. in RDBMS's).

    I have one table, partrev, that stores revision levels for part numbers. Each revision level has an effective date. There may be many records for each part number, due to expired effective dates.

    I want to display ONE record for each part number; the most recent revision with an effective date <= today. SELECT partnum, revisionnum
    FROM dbo.partrev
    WHERE (partrev.partnum IN
    (SELECT MAX(effectivedate) AS Expr1
    FROM dbo.partrev AS partrev_1
    WHERE (effectivedate <= GETDATE())
    GROUP BY partnum))

    When I run this as an SQL query, it gives me the conversion error listed in the subject of this post. What am I doing wrong???

    Thanks,
    M.Taft

  • Im guessing that partrev.effectivedate is a varchar(x) and at least one row contains data that is not a date.

    Try

    Select * from PartRev where ISDATE(effectivedate) = 0

    To isolate the 'corrupt' row(s)



    Clear Sky SQL
    My Blog[/url]

  • Hi Dave, and thanks for taking the time to reply!

    I ran that query and it returned 0 rows. Argh!

  • Sorry , no your query is wrong ,

    its attempting to convert Partnum to a dateTime due to this

    partrev.partnum IN

    (SELECT MAX(effectivedate)

    From what you have described you query should be more like this

    Select Partnum,revisionnum,max(effectivedate)

    from partrev

    group by Partnum,revisionnum

    Post DDL , Sample data and expect output if not



    Clear Sky SQL
    My Blog[/url]

  • Hi Dave, and thanks again...

    I tried this but since it groups by revisionnum, it simply gave me one row for each partnum/revisionnum/effectivedate. I'm actually looking for the very LAST revisionnum by effectivedate.

    For example, if there are 3 records for part number 1234, they might look like this:

    partnum revisionnum effectivedate

    ---------- ----------- -------------

    1234 A 01/23/2007

    1234 44-D 02/20/2008

    1234 3 03/31/2009

    I only want to display the very last record, the one with an effective date of 03/31/2009.

    I ended up using this query and it works exactly as I wanted:

    SELECT partrev.partnum, partrev.revisionnum, partrev.effectivedate

    FROM dbo.partrev AS partrev INNER JOIN

    (SELECT partnum, MAX(effectivedate) AS effectivedate

    FROM dbo.partrev AS partrev_1

    WHERE (effectivedate <= GETDATE())

    GROUP BY partnum) AS SelectTbl ON partrev.partnum = SelectTbl.partnum AND partrev.effectivedate = SelectTbl.effectivedate

    Thanks again for taking the time to reply!

  • Glad you worked it out , if you are using 2005+ there is a smarter way

    with cteData(partnum, revisionnum, effectivedate,rownum)

    as(

    SELECT partrev.partnum, partrev.revisionnum, partrev.effectivedate, row_number() over (partition by partrev.partnum, partrev.revisionnum order by partrev.effectivedate)

    FROM dbo.partrev

    where effectivedate <= GETDATE()

    )

    select * from cteData where rownum =1

    I think thats should have the same effect



    Clear Sky SQL
    My Blog[/url]

  • be careful with your "MAX" statement since getting that conversion error means that table is storing dates as STRINGS

    you shown this in one of your replies

    partnum revisionnum effectivedate

    ---------- ----------- -------------

    1234 A 01/23/2007

    1234 44-D 02/20/2008

    1234 3 03/31/2009

    the problem is your date formats of MM/DD/YYYY

    The MAX aggregate does string comparisons from left to right. 12/31/1900 is greater than 01/01/2010 because the first characters are 1 and 0 and the 1 is greater than the 0 on the ascii table

    instead do this

    MAX(convert(datetime,MyStringDate))

    this way it converts the string to a datetime before comparing and will correctly compare the dates.

Viewing 7 posts - 1 through 6 (of 6 total)

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