Importing DateTime from TXT File 1

[MeltdownMX]

Hi,

We have this text file with Date/Time information in the format of 050401172100.

050401172100 Meaning:

05 - Year 2005
04 - Month April
01 - Day The 1st
17 - 24 Hour time 5p.m.
21 - Minutes 5:21p.m.
00 - Seconds 5:21:00 p.m.

We have been trying to figure out how to successfully import this into SQL and have failed miserably every time.

Sometimes we get "Arithmetic overflow error converting expression to data type datetime."

What format does SQL want the DateTime string of text to be in so that it can be successfully converted? Thanks a lot.

 

[dbomrrsm]

select cast('20'+Left('050401172100',2)+'-'
+substring('050401172100',3,2)+'-'
+substring('050401172100',5,2)+' '
+substring('050401172100',7,2)+':'
+substring('050401172100',9,2)+':'
+substring('050401172100',11,2)
as datetime)

will have to be careful with the year as it might be 1999 and the above will put the teat as 2099.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

Sorry import it as it is and then update the field using above code.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[jdgonzalez]

Depending on the size of the file, you could use active x in your ETL process. However, you'll take a performance hit in importing your data. In the DTS designer, there's a date time wizard you can use in your transformation.

 

[MeltdownMX]

Hi DBomrrsm, Thanks for the reply.

I ran this Query in the Query Analyzer and it does exactly what I need.

select cast('20'+Left('050401172100',2)+'-'
+substring('050401172100',3,2)+'-'
+substring('050401172100',5,2)+' '
+substring('050401172100',7,2)+':'
+substring('050401172100',9,2)+':'
+substring('050401172100',11,2)
as datetime)

How would I update the 96000 records that I have so that all of my data which is like 050401172100 are converted into 2005-04-01 17:21:00.000? Is there an update query to run?

I am not familier with SQL transformations like this using SQL. Usually I used Web-Based conversions with ColdFusion so I am not sure how a query would be written to do this dynamically for all our records. Thanks.


JDGonzalez, We are importing the data in from a txt file and as far as a date time wizard, we havent seen anything I will look at the DTS designer though and see what we come up with. Thank you.

 

[dbomrrsm]

create a new field in the table as a datetime data type

update your table
set yournewfield = 
cast('20'+Left('050401172100',2)+'-'
+substring('050401172100',3,2)+'-'
+substring('050401172100',5,2)+' '
+substring('050401172100',7,2)+':'
+substring('050401172100',9,2)+':'
+substring('050401172100',11,2)
as datetime)

replacing the '050401172100' with the field name that holds the '050401172100'.

good luck.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[MeltdownMX]

Okay, I think I am one step closer, I got the error:

Server: Msg 241, Level 16, State 1, Line 13
Syntax error converting datetime from character string.

Does my data field have to be a certain type?

Right now it is just (char). Should it be varchar? Would that make a difference?

 

[MeltdownMX]

Cool, I figured it out. I was being stupid and not taking out the Single Quotes!!

Hate it when that happens.


THANKS!!

 

[dbomrrsm]

No - char is fine - what is happening is that some of your dates when the conversion is taking place are not convertable to datetime i.e. you may have a 13 in positions 3 and 4 and no month is 13.

you can do

select cast('20'+Left('050401172100',2)+'-'
+substring('050401172100',3,2)+'-'
+substring('050401172100',5,2)+' '
+substring('050401172100',7,2)+':'
+substring('050401172100',9,2)+':'
+substring('050401172100',11,2)
as datetime)
where isdate(cast('20'+Left('050401172100',2)+'-'
+substring('050401172100',3,2)+'-'
+substring('050401172100',5,2)+' '
+substring('050401172100',7,2)+':'
+substring('050401172100',9,2)+':'
+substring('050401172100',11,2)
as datetime)) = 1

this will only update the new field where the date is convertable to a datetime - you can then do a select where the newfield is null to see which records havent been converted and why.

alternatively if you want to know this before doing an update you can do

select *,
isdate(cast('20'+Left('050401172100',2)+'-'
+substring('050401172100',3,2)+'-'
+substring('050401172100',5,2)+' '
+substring('050401172100',7,2)+':'
+substring('050401172100',9,2)+':'
+substring('050401172100',11,2)
as datetime))
from yourtable
where cast('20'+Left('050401172100',2)+'-'
+substring('050401172100',3,2)+'-'
+substring('050401172100',5,2)+' '
+substring('050401172100',7,2)+':'
+substring('050401172100',9,2)+':'
+substring('050401172100',11,2)
as datetime)= 0

This will show the rows that wont work.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]