import datetime conversion problem.
-
basically i'm importing from a .csv file with values like this:
2006-05-11-16.29.43.882000
and i'm trying to import them into a table column with values like this:
2006-05-02 09:14:40.593
i'm having a conversion error. i thought sql would know what to do.
is this some thing simple? thoughts?
_________________________
-
ok... so i import them into their own table. all data was imported
into a table with varchar datatype.
then it try using that table to import from.
no problem right. i'll just change the varchar to 'datetime' so sql
flashed an warning about data conversion. i click ok, and to
proceed, and this is what i get next.
'mytable' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
now what?
_________________________
-
so here is what i do next...
i keep the same import table as i made before. then change the datatype
to 'timestamp', and get the same warning message about conversion, and i
click ok to proceed. it worked. so i go to query the date values, and
here is what i get. some thing like:
ac0000000000001234
what in the world?? i figure i convert from the 'timestamp' into the 'datetime'
datatype. get the warning, and i proceed, and it worked! this time the values
are all messed up. the dates are like this:
1900-01-01 00:00:13.747
bizarre.
thoughts on how i can convert those original values over to 'datetime' ?
_________________________
-
TIMESTAMP has nothing to do with the date and/or time. It is a 'versioning' value used by Microsoft SQL Server.
SQL Server has no idea how to interpret this value: 2006-05-11-16.29.43.882000
The only thing it can tell is that appears to be 2006-May-11 at 4:26:43.882000 PM. But it only recognizes milliseconds to three places. Also that extra dash between the day (11) and the hour (16) and the periods (.) between the hours, minutes, seconds, milliseconds throws it off (SQL Server expects colons (
between the time values).You probably need to import it as a VARCHAR, then create a user-defined function to convert it to a recognizable date and time value, then convert that to datetime.
-SQLBill
-
cool. thanks for the advice, but creating a user-defined function
is beyond my current level of experience with this.
i'll check out some resources and see what i can do.
thanks again.
_________________________
-
DECLARE @testDate AS DATETIME
DECLARE @stringDate AS VARCHAR(25)
SET @StringDate='2006-05-11-16.29.43.882000'
SET @StringDate=STUFF(STUFF(STUFF(LEFT(@StringDate,23),11,1 ,' '),14,1,':'),17,1,':')
PRINT @StringDate
SET @testDate=@StringDate
PRINT @testDate
--you can run after import
--UPDATE MyTable SET myStringDate=STUFF(STUFF(STUFF(LEFT(myStringDate,23),11,1 ,' '),14,1,':'),17,1,':')
--and you can change the datatype
Kindest Regards,Vasc
-
many thanks veteran,
i would have to run this for each and every record that contained
the date like it is.
quick question... when you say (stuff)... thats supposed to be what
exactly?
_________________________
-
STUFF is in BOL
you need to run the STEP ONCE
if you want you can add the transformation after your import
"UPDATE MyTable SET myStringDate=STUFF(STUFF(STUFF(LEFT(myStringDate,23),11,1 ,' '),14,1,':'),17,1,':')"
since I don t know your DDL I can t post the exact statement that you need to run
Kindest Regards,Vasc
-
cool! yeah i'm looking it over now.
wasn't sure stuff was an official name, but now i know.
thanks again for the help.
i'm running some experiments with this script.
you rock!
_________________________
-
excellent... works great!
_________________________
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply