none
Data Type Conversion RRS feed

  • Question

  • Dear All,
    i'm new in mssql so please help me resolve my problem :D

    i want to convert field with char(10) datatype to smalldatetime data type.

    let say i have a table like this

    name : table_a
    id int(4),
    date_joint1 char(10)
    date_join2 (smalldatetame)

    i want to update tabel field date_join2 an put date_joint1 content to it but since they data type is diferent so i got error for the result..
    UPDATE    table_a
    SET              date_join2 = CONVERT(smalldatetime, date_joint1)

    error msg : the conversion of char data type to smalldatetime data type resulted in out of range smalldatetime value.



    can somebody help me please :)
    thanks alot.

    Sunday, April 12, 2009 6:47 PM

Answers

  • @c_shah:
    I see your point and why not take it a step further, like this:

    USE
    tempdb; CREATE TABLE table_a ( id int identity(1, 1), date_joint1 char(10), date_join2 smalldatetime NULL ) INSERT INTO table_a SELECT '20090404', NULL UNION ALL SELECT '20090405', NULL UNION ALL SELECT '20890105', NULL UNION ALL SELECT '10890105', NULL; UPDATE table_a SET date_join2 = CONVERT(smalldatetime, date_joint1) WHERE CASE ISDATE(date_joint1) WHEN 1 THEN CASE WHEN CONVERT(datetime, date_joint1) > '01/01/1900'
    AND CONVERT(datetime, date_joint1) < '06/06/2079' THEN 1 ELSE 0 END WHEN 0 THEN 0 END = 1; DROP TABLE table_a;



    Now we check for both valid dates and that they are within the smalldatetime range.
    Tuesday, April 14, 2009 12:30 PM

All replies

  • Add an WHERE statement where you control if date_joint1 is a valid date.

    UPDATE
    table_a SET date_join2 = CONVERT(smalldatetime, date_joint1) WHERE (ISDATE(date_joint1) = 1);

    Then you can run the following query to check out these date_joint1 which aren't valid dates.

    SELECT
    date_joint1 FROM table_a WHERE (ISDATE(date_joint1) = 0);
    Sunday, April 12, 2009 7:50 PM
  • The error message is saying that you have  a datetime value inside your date_joint1 field which is an out of range value for smalldatetime value.

    For e.g. SELECT CONVERT(smalldatetime, '18991231')will return same error
     
    please check values inside your  date_joint column is between January 1, 1900, through June 6, 2079

    run the following query and find the values that are out of range

    SELECT id, date_joint1 FROM table_a
    WHERE CONVERT(datetime, date_joint1) >  '06/06/2079' 
    OR  CONVERT(datetime, date_joint1) < '01/01/1900'
    Sunday, April 12, 2009 8:01 PM
  • Hakan, , if date_joint1 is all valid date stored as char(10)
     then your solution may generate an error.

    create table table_a
    (
     id int identity(1,1)
     , date_joint1 char(10)
     , date_join2 smalldatetime null)
    Go

    insert into table_a
    select '20090404',null
    union all
    select '20090405',null
    union all
    select '20890105',null

    This should work

    UPDATE   table_a
    SET              date_join2 = CONVERT(smalldatetime, date_joint1)
    WHERE CONVERT(datetime, date_joint1) > '01/01/1900'
    AND  CONVERT(datetime, date_joint1) <  '06/06/2079'

    Sunday, April 12, 2009 8:15 PM

  • What is the dateformat that you are storing in the  date_joint1 field?

    When  date_joint1  is converted to datetime , it might be wrong date which is not valid.


    for example:
    if date_joint1 contains wrong values for MM or DD or YY in the MM/DD/YY format the you will get the above error while converting to datetime.

    can you verify the date stored in date_joint1 field ?

    Chandra, http://www.ggktech.com
    Monday, April 13, 2009 12:51 PM
  • @c_shah:
    I see your point and why not take it a step further, like this:

    USE
    tempdb; CREATE TABLE table_a ( id int identity(1, 1), date_joint1 char(10), date_join2 smalldatetime NULL ) INSERT INTO table_a SELECT '20090404', NULL UNION ALL SELECT '20090405', NULL UNION ALL SELECT '20890105', NULL UNION ALL SELECT '10890105', NULL; UPDATE table_a SET date_join2 = CONVERT(smalldatetime, date_joint1) WHERE CASE ISDATE(date_joint1) WHEN 1 THEN CASE WHEN CONVERT(datetime, date_joint1) > '01/01/1900'
    AND CONVERT(datetime, date_joint1) < '06/06/2079' THEN 1 ELSE 0 END WHEN 0 THEN 0 END = 1; DROP TABLE table_a;



    Now we check for both valid dates and that they are within the smalldatetime range.
    Tuesday, April 14, 2009 12:30 PM