Importing DateTime from TXT File

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Importing DateTime from TXT File

Author

Topic

Barbie
Starting Member

2 Posts

Posted - 2005-04-18 : 14:56:52

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.

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

Sometimes I 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? I have 96000 records that need to be converted. Please help.

Thanks a lot.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-18 : 22:27:26

for valid dates, search Convert in BOL

--------------------
keeping it simple...

dineshasanka
Yak Posting Veteran

72 Posts

Posted - 2005-04-19 : 03:17:02

[code]
declare @st as varchar(12)
declare @y as varchar(2)
declare @m as varchar(2)
declare @d as varchar(2)
declare @h as varchar(2)
declare @n as varchar(2)
declare @s as varchar(2)
declare @vaDate as varchar(30)
select @st = '050401172100'
Set @y = left(@st,2)
Set @m = substring(@st,3,2)
Set @d = substring(@st,5,2)
Set @h = substring(@st,7,2)
Set @n = substring(@st,9,2)
Set @s = substring(@st,11,2)
set @vaDate = '20' + @y + '/' + @m + '/' + @d + ' ' + @h + ':' + @n + ':' + @s

select convert (smalldatetime,@vaDate)
[/code]

quote:
Originally posted by Barbie

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.

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

Sometimes I 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? I have 96000 records that need to be converted. Please help. Thanks a lot.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-04-19 : 04:30:10

change the script in transformation during dts to:

quote:

'**********************************************************************
' Visual Basic Transformation Script
' Copy each source column to the
' destination column
'************************************************************************

Function Main()

dim condate
dim mydate

mydate=dtssource("col001") --source column
condate="20" + mid(mydate,1,2) + "/" + mid(mydate,3,2) + "/" + mid(mydate,5,2) + " " + mid(mydate,7,2) + ":" + mid(mydate,9,2) + ":" + mid(mydate,11,2)

DTSDestination("dtime") = condate --destination column
Main = DTSTransformStat_OK
End Function

format for date time from text file is (yyyy-mm-dd hh:mm:ss.fffffffff)

--------------------
keeping it simple...

Barbie
Starting Member

2 Posts

Posted - 2005-04-19 : 10:39:54

Hi guys,

Thanks for all your responses. They really helped. I really liked dashasanka's method to convert to a smalldatetime. I also found a way for converting to datetime. Thanks again.

quote:
update tablename
set newfieldname =
cast('20'+Left('currentfield',2)+'-'
+substring('currentfield',3,2)+'-'
+substring('currentfield',5,2)+' '
+substring('currentfield',7,2)+':'
+substring('currentfield',9,2)+':'
+substring('currentfield',11,2)
as datetime)

dineshasanka
Yak Posting Veteran

72 Posts

Posted - 2005-04-20 : 00:22:14

yeah that will be fair enough.
I put those in separete line just for better understandig.
Good Luck

quote:
Originally posted by Barbie

Hi guys,

Thanks for all your responses. They really helped. I really liked dashasanka's method to convert to a smalldatetime. I also found a way for converting to datetime. Thanks again.

quote:
update tablename
set newfieldname =
cast('20'+Left('currentfield',2)+'-'
+substring('currentfield',3,2)+'-'
+substring('currentfield',5,2)+' '
+substring('currentfield',7,2)+':'
+substring('currentfield',9,2)+':'
+substring('currentfield',11,2)
as datetime)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources