Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    32

    Unanswered: Comparing a real datetime to a 'constructed' datetime

    I have the following SQL:

    select convert(datetime,'04-20-' + right(term,4)) as dt,
    'Deposit' as type, a.* from
    dbo.status_view a

    where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.

    The SQL above returns

    2004-04-20 00:00:00.000 Deposit ...

    Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:

    select * from
    (
    select convert(datetime,'04-20-' + right(term,4)) as dt,
    'Deposit' as type, a.* from
    dbo.status_view a
    ) where dt >= a.submit_date

    I get the following error:

    Syntax error converting datetime from character string.

    Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What does:

    SELECT * FROM status_view
    WHERE ISNUMERIC(RIGHT(Term,4))=0

    return for you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    32
    Quote Originally Posted by Brett Kaiser
    What does:

    SELECT * FROM status_view
    WHERE ISNUMERIC(RIGHT(Term,4))=0

    return for you?
    If I do:

    SELECT top 10 right(term,4) FROM dbo.status_view

    I get:

    2003
    2002
    2003
    2002
    2003
    2003
    2001
    2002
    2003
    2001

    But if I do:

    SELECT top 10 right(term,4) FROM dbo.status_view
    WHERE
    isnumeric(right(term,4)) =0 and

    It returns no rows! I think you're on to something. But the weirdest part is if I do:

    SELECT top 10 cast(right(term,4) as int) FROM dbo.status_view

    I still get:

    2003
    2002
    2003
    2002
    2003
    2003
    2001
    2002
    2003
    2001

    Ack!

  4. #4
    Join Date
    Jan 2004
    Posts
    32
    Quote Originally Posted by Brett Kaiser
    What does:

    SELECT * FROM status_view
    WHERE ISNUMERIC(RIGHT(Term,4))=0

    return for you?
    Oh wait, I was doing one and not zero. If I do:

    SELECT count(1) FROM dbo.status_view
    WHERE
    and isnumeric(right(term,4)) = 0

    it returns zero. So I guess thats not it...

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well that just means it doesn't see anything that it doesn't think that a number...

    What about...

    SELECT * FROM status_view
    WHERE CONVERT(int,RIGHT(Term,4))<1784
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2004
    Posts
    32
    Quote Originally Posted by Brett Kaiser
    Well that just means it doesn't see anything that it doesn't think that a number...

    What about...

    SELECT * FROM status_view
    WHERE CONVERT(int,RIGHT(Term,4))<1784
    This works fine. It returns no rows, but no errors. This is driving me insane!

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    This is your problem, right?

    Code:
    select * from
    (
    select convert(datetime,'04-20-' + right(term,4)) as dt,
    'Deposit' as type, a.* from
    dbo.status_view a
    ) where dt >= a.submit_date
    Which is syntactically incorrect....

    what is a.submit_date? I assume it's from select *

    Plus you need ro give the derived table a name

    Code:
    select * from
    (
    select convert(datetime,'04-20-' + right(term,4)) as dt,
    'Deposit' as type, a.* from
    dbo.status_view a
    ) AS XXX
    where dt >=  submit_date
    Does that run?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •