Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Question Unanswered: How To Solve An Error While Converting Nvarchar To Datetime

    HI,
    I HAVE BEEN TRYING TO TRANSFORM AN OLD TABLE TO A NEW FORMAT AND CHANGE SOME OF THE DATATYPE FORMATS USED IN THE OLD ONE.
    OUT OF WHICH ONE IS A COLUMN CALLED AS FORM_RECEIVE_DATE WHICH HAS NVARCHAR(41) AS DATATYPE IN THE OLD TABLE CREATED BY A PREVIOUS DBA (DON'T KNOW wHY?)

    wHILE CONVERTING IT INTO DATATYPE DATETIME , I AM GETTING THIS ERROR :- "Arithmetic overflow error converting expression to data type datetime." i DON'T KNOW WHY

    hERE ARE FEW EXAMPLES OF THE DATA CONTAINED IN THE PREVIOUS TABLE :-

    05082003
    05062003
    05142003

    COULD YOU PLS TELL ME A WAY TO SOLVE THIS ?

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

    How about

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 NVARCHAR(41))
    GO
    
    INSERT INTO myTable99
    SELECT '05082003' UNION ALL
    SELECT '05062003' UNION ALL
    SELECT '05142003'
    GO
    
    SELECT CONVERT(datetime,SUBSTRING(Col1,1,2)+'/'+SUBSTRING(Col1,3,2)+'/'+SUBSTRING(Col1,5,4))  FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    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
    Apr 2004
    Posts
    49

    Thumbs up Re: How To Solve An Error While Converting Nvarchar To Datetime

    HI,

    THANKYOU VERY MUCH BRETT FOR THE FAST REPLY ,IT WAS OF GREAT HELP AS I USED :-

    CONVERT(datetime,SUBSTRING(Col1,1,2)+'/'+SUBSTRING(Col1,3,2)+'/'+SUBSTRING(Col1,5,4))

    TO SOLVE MY PROBLEM AS YOU HAVE ASKED ME TO DO.

    AND WHEW !!! IT ALL SOLVED IN A JIFFY.

    BUT FOR CURIOSITY SAKE :-

    I WOULD LIKE YOU TO EXPLAIN ME :-

    SELECT '05082003' UNION ALL
    SELECT '05062003' UNION ALL
    SELECT '05142003'

    WHAT DO YOU MEAN BY UNION ALL WHILE INSERTING IT AND ANY SPECIAL REASON TO USE TABLE NORTHWIND WHILE USING IT.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you use Query Analyzer?

    Just cut and paste the code in to the window to see what it does...

    Northwind is a sample database that comes with the install of swl server...

    I use that db so people can see examples...
    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.

  5. #5
    Join Date
    Apr 2004
    Posts
    49
    HI BRETT,

    THANK YOU VERY MUCH,IT WAS FANTASTIC OF YOU TO SOLVE THINGS SO QUICKLY AND YES I UNDERSTOOD YOUR CODE.
    i JUST NEED TO ASK YOU IS THERE ANY FREE TUTORIALS IN THE WEB BY WHICH I CAN LEARN TO CODE COMPLEX QUEIRES , THE WAY YOU CAN DO IT SO EASILY..
    I WOULD BE OBLIGED IF YOU COULD JUST HELP ME OUT IN THIS REGARD.

Posting Permissions

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