Convert to DateTime

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

From: Elmo Watson (sputnik75043_at_nospam.Yahoo.com)
Date: 03/01/04 

Date: Sun, 29 Feb 2004 19:01:33 -0600

Unfortunately, I've inherited a database with a field which has been adding
dates and times, but it's been using an nvarchar field .

Most of the strings are in this format:
Mon Oct 13 12:58:00 2003 EST

but of course, there are still strings that will convert to date and time
more easily like:
4/11/2003 9:29:53 AM

I've tried doing a left/right/update scenario to remove the Day and EST
strings, but then, I'm left with strings like:
Oct 13 12:58:00 2003

I really would like to convert it to a date/time field, but even with that,
apparently it doesn't like the Oct and gives me an error:
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error
converting datetime from character string.

Anyone have any ideas on how I can do this in as few steps as possible?

Relevant Pages

  • Re: [ANN] Data Source Name parser (ODBC etc.)
    ... > suggested URL-like strings describing database connections. ... > library for such strings (data source names) is now available at ... raises the exception Syntax_Error. ...
    (comp.lang.ada)
  • Question on how updates to Access tables may or may not cause the database to expand in size
    ... All strings are stored as variable length data, one byte for each actual character and one overhead byte. ... When a database is compacted and repaired, tables are written out in primary key sequence. ... I have concluded that after the compact/repair, that each table, is written in sequence, filling up 2k pages, and that tables are not generally intermixed except at the end of one table and the beginning of the next. ... then the database DOES expand in size. ...
    (comp.databases.ms-access)
  • Re: Japanese to Unicode characters
    ... Oracle-Rdb database; a VB application running on a Window98 Japanese PC ... store the japanese strings as Unicode. ... UniToolBox component for VB which handle the Unicode strings. ...
    (microsoft.public.sqlserver.programming)
  • Re: i18n hell
    ... table attributes to UTF-8 only garbage kept adding into the database. ... you using unicode strings or byte strings? ...
    (comp.lang.python)
  • Re: PreparedStatement
    ... It actually depends upon what JDBC driver ... But the "generic database access" class I am using to insert data to the ... to insert Strings into numeric fields (as long as the Strings are numbers, ...
    (comp.lang.java.help)