Re: converting from varchar to datetime

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Joseph Dixon (joseph_dixon_at_hotmail.com)
Date: 06/27/04 

Date: Sun, 27 Jun 2004 11:25:34 -0700

I Have just ran into the same problem converting a nvarchar to
datetime.. The following may help..

--------------------------------
From: Joseph Dixon
I don't know if this is still relevent to what you are working on, but I
also recieved this message.
This error message indicates that one of your date fields date is not
consistant with date values, And the SQL server is having problems
converting what should be in date format to a date.

:::::::::PROBLEM:::::::::
Example of incorrect information in your datebase:

wrong: 2/200/4
wrong: 2/2/204

The SQL server will read these dates as Arithmatic operations, instead
of dates, and will generate this error.

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

::::::::RESOLUTION:::::::

If you understand this you know that you have some cleanup to do on your
data before you convert to datetime. Look for fields that have incorrect
formats. I exported to MS access and worked with the data from their
thats how I discovered the problem.

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: HELP with DateAdd statement and SQL
    ... saying SQL that it is having a problem converting the charater set. ... > do datetime comparisons with character data without converting the character ... > the performance of any query that you run agains the table, ... > an example of one of the UTC entries and I'll show you how to do it. ...
    (microsoft.public.scripting.vbscript)
  • Re: date variable in a DTS package
    ... The expressions you're converting to ... datetime are integers, and format 112 is for converting strings in the ... format code for datetime strings is only documented for conversions between ...
    (microsoft.public.sqlserver.programming)
  • Re: Typed Dataset: Add expression-based column?
    ... datetime field), I get an error at runtime complaining of a problem ... "Conversion failed when converting datetime from character string" ... Should I be trying to architect a solution to this in the SQL query? ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: HELP with DateAdd statement and SQL
    ... Yes, I will need help with converting the time, ... query and have a new column equal to the TimeWritten column? ... > do datetime comparisons with character data without converting the character ...
    (microsoft.public.scripting.vbscript)
  • Re: Converting a nvarchar to a date
    ... "William Gower" wrote in message ... > neither of the answers work, I get an "Arithmetic overflow error ... > expression to data type smalldatetime. ...
    (microsoft.public.sqlserver.programming)