Re: Convert to DateTime
From: Andrew John (aj_at_DELETEmistrose.com)
Date: 03/01/04
- Next message: Utada P.W. SIU: "Change user"
- Previous message: Steve Kass: "Re: Convert to DateTime"
- In reply to: Elmo Watson: "Convert to DateTime"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 1 Mar 2004 12:29:40 +1100
Elmo,
A straightforward way is to add a new datetime column to the table,
and run a succession of update queries from the old nvarchar to the new.
Easy to see when you have got them all by allowing NULLs in the new
column and doing selects to see where still null.
Query e.g.
Update MyTable
set NewDTCol = convert( datetime, substring( OldNVCCol, 21, 4) + '-10-' + substring( OldNVCCol, 9, 10) , 120 )
where substring( OldNVCCol, 5, 3 ) = 'Oct'
and NewDtCol is NULL -- Not really required because of the 'Oct', but potentially prevents errors for the easier
non-matching formats that have already been converted
If you want to do all months in one hit, then you could use CASE.
Regards
AJ
"Elmo Watson" <sputnik75043@nospam.Yahoo.com> wrote in message news:e0KB9gy$DHA.392@TK2MSFTNGP12.phx.gbl...
> 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?
>
>
>
- Next message: Utada P.W. SIU: "Change user"
- Previous message: Steve Kass: "Re: Convert to DateTime"
- In reply to: Elmo Watson: "Convert to DateTime"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
