Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73

    Unanswered: DateTime Update Statement

    I've looked through books online and can't understand why the below statement doesn't work:

    update tblregionalmarketrate
    set effectivedate = '2003-27-01 00:00:00.000'
    where effectivedate > '2003-01-01 00:00:00.000'

    The error returned is:

    Server: Msg 242, Level 16, State 3, Line 1
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    The statement has been terminated.


    The field EffectiveDate is a datetime field, I've tried using variables, datediff statements, several other options, and still get the same error. That being so I know it has to be something simple being overlooked.

    Thanks,

    Brent

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

    Re: DateTime Update Statement

    Originally posted by baolive
    I've looked through books online and can't understand why the below statement doesn't work:

    update tblregionalmarketrate
    set effectivedate = '2003-27-01 00:00:00.000'
    where effectivedate > '2003-01-01 00:00:00.000'

    The error returned is:

    Server: Msg 242, Level 16, State 3, Line 1
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    The statement has been terminated.


    The field EffectiveDate is a datetime field, I've tried using variables, datediff statements, several other options, and still get the same error. That being so I know it has to be something simple being overlooked.

    Thanks,

    Brent

    What's the name of Month 27?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: DateTime Update Statement

    Cut and paste this:

    (Do code tags work here?)

    Code:
    USE Northwind
    GO
    CREATE TABLE tblregionalmarketrate (effectivedate datetime)
    GO
    INSERT INTO tblregionalmarketrate (effectivedate)
    SELECT GetDate() UNION ALL
    SELECT GetDate() UNION ALL
    SELECT GetDate() UNION ALL
    SELECT GetDate() UNION ALL
    SELECT GetDate()
    GO
    SELECT * FROM tblregionalmarketrate
    GO
    
    UPDATE tblregionalmarketrate
       SET effectivedate = '2003-01-27 00:00:00.000'
     WHERE effectivedate > '2003-01-01 00:00:00.000' 
    GO
    
    SELECT * FROM tblregionalmarketrate
    GO
    
    DROP TABLE tblregionalmarketrate
    GO

  4. #4
    Join Date
    Sep 2002
    Location
    Mancos, CO
    Posts
    73
    Thanks, see told you it would be simple, just had the month and day backwards. Could have sworn I tried it with the correct format before.

Posting Permissions

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