Date conversion - SQL Server Forums

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

SQL Server Development (2000)

Date conversion

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2004-02-06 : 13:03:13

I am trying to import a text file that has a field formated like:

dateField
-------
122002
10252003
1162002
12292001

How do I import this into a field with date format?

Thanks!
Lane

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-06 : 13:04:49

One option would be to import the data as varchar into an intermediate table then convert the values on insert into base table.

create table import (dateField varchar(20))

insert into import 
select '122002' union all
select '10252003' union all
select '1162002' union all
select '12292001'

select convert(datetime,datefield)
from
(
select 
	case 
		when len(datefield) = 6 then left(datefield,2) +'/'+substring(datefield,2,2) + '/' + right(datefield,2)
		when len(datefield) = 8 then left(datefield,2) +'/'+substring(datefield,2,2) + '/' + right(datefield,4)
		when len(datefield) = 7 then left(datefield,1) +'/'+substring(datefield,2,2) + '/' + right(datefield,4)
	end datefield
from import
) d

drop table import

EDIT: Changed to make day 2 digits

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-06 : 13:11:43

How are you going to know which date this is:

1212003

Is it January 21st or December 1st? It looks like you've got a problem that is going to require manual work.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-06 : 13:21:05

Good point Tara, Also, what is 1162002: 1/16/2002 or 11/6/2002 ??

lane0618
Posting Yak Master

134 Posts

Posted - 2004-02-06 : 13:22:03

Oops, my bad.

the day part of the date is always two digit, so 1212003 would have been 12012003. Hope that clears things up.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-06 : 13:25:18

So will there be any not valid dates? Could you show us some more sample rows? And after each, specify what date it is in words, like:

12122003 is December 12th, 2003.

We need to determine if you are going to have a problem with any dates.

Tara

lane0618
Posting Yak Master

134 Posts

Posted - 2004-02-06 : 13:36:59

Here's a sample list:

8112000
8312001
5152003
12052002
3162001
12271999
9022003
4082000
7272003
10312002
10112001
3032003
4052002
4252003
1082002
1282003
11182000
9212001
10032003
7212000
6052003
1222000
3082003
9192002
11152002
10162001
1052004
3222001
6172002
11051999
11012001
10252002
5182001
2182003
10282000
12282001
8172003
1042001
4092003
9302002
11192003
8162000
2172000
1162004
8292002
11062001
1232003
1102001
6082001
11111999
7222003
7022002
2072003
1032002
8062003
9242002
9012000
12092003
7262000
3252002
10092002
6122002
3272001
9262001
4242000
1272000
3022000
12202003
12082000
10202002
8122003
10222001
12011999
5102003
2132003
1312001
10042002
8142001
6292001

As you can see, the day part always has two digits, even if the day is less than 10 (ie 04 = 4th, 08 = 8th) - so the dates will always be valid. Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-02-06 : 13:40:21

Then you can use ehorn's method. You'll need to import your data into a staging table first. The datetime column though would be VARCHAR(8). Then you run his query pointing to your staging table (he calls it import).

Tara

lane0618
Posting Yak Master

134 Posts

Posted - 2004-02-06 : 17:03:00

That works, but is there a way to update the records already in the table? With ehorn's solution, I can't seem to output more than just the date field being processed? So I get a list of dates, but no key to tie them back.
Thanks

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-02-06 : 17:29:15

The query above can be modified to perform all your conversion logic. I only demonstrated how to convert the dates, but if this is the only field that needs converting then just add the other fields to the derived query and perform an insert:

INSERT INTO <base table> VALUES (<your values>)
SELECT <the other fields>,convert(datetime,datefield)
FROM
(
select <the other fields>,
	case 
		when len(datefield) = 6 then left(datefield,2) +'/'+substring(datefield,2,2) + '/' + right(datefield,2)
		when len(datefield) = 8 then left(datefield,2) +'/'+substring(datefield,2,2) + '/' + right(datefield,4)
		when len(datefield) = 7 then left(datefield,1) +'/'+substring(datefield,2,2) + '/' + right(datefield,4)
	end datefield
from import
) d

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources