Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Red face Unanswered: Date Conversion Problem

    Hi,

    I've got a table with the columns DAY, MONTH and YEAR, all of them are numeric. I want to join them and store in a single datetime column.

    I've been trying several ways to do that, but unfortunately I receive the same error :

    "[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

    Probably I don't know how to use the CONVERT/CAST function properly.

    Any ideas?

    (Thanx)

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No, this error normally means that you are trying to convert values that are not an actual date.

    11/31/03, or 2/29/2003, or 13/05/04, for example.

    This happens when you have a database that does not store values in datetime format. It can also happen if you supply a day, month, and year value to SQL server in 2-digit formats and SQL server is unsure which value represents which part of the the date.

    What code are you using for your conversion? You could wrap the ISDATE() function around it and query against your source table to find any values that it cannot convert.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Dec 2003
    Posts
    454
    CAST(columnMonth AS Char(2)) + '/' + CAST(columnDay AS Char(2)) + '/' + CAST(columnYear AS Char(4))

    returns the string which can be inserted into the field with DATETIME datatype

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    STR(columnMonth, 2) + '/' + STR(columnDay, 2) + '/' + STR(columnYear, 4)

    should work too.

Posting Permissions

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