Import date ddmmyyyy 1

[micang]

SQL 2000

Hi All,

I import a large flatfile (37 million rows) into SQL using bulkinsert and a formatfile.

I have a few columns that are dates, but they are in the format of ddmmyyyy.

Currently I am importing them them to a varchar 8 column. Then I thought of converting them to a datetime, but it's proving tedious as updating 37 million rows is taking forever.

I was wondering if there is a way of importing the ddmmyyyy directly in to a datetime field?

Any info appreciated.

Michael

 

[AlexCuse]

If you are using DTS, you could use VBScript's format() function to get the date into YYYYMMDD format and put that directly into a datetime column.

How are you importing it though?

Hope this helps,

Alex

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

 

[micang]

Hi Alex,

I am using bulk insert and a format file:

BULK INSERT  sad
    FROM 'E:\sad_01' 
    WITH 
    (formatfile = 'E:\b.fmt')

and format file:

8.0
30
1	SQLCHAR	0	1	""	1	a	""
2	SQLCHAR	0	2	""	2	strMakeCode	""
3	SQLCHAR	0	3	""	3	strModelCode	""
4	SQLCHAR	0	20	""	4	strVehicleDescription	""
5	SQLCHAR	0	2	""	5	strBodyCode	""
6	SQLCHAR	0	2	""	6	strColourCode	""
7	SQLCHAR	0	5	""	7	strEngineCapacity	""
8	SQLCHAR	0	2	""	8	strSeatingCapacity	""
9	SQLCHAR	0	1	""	9	strPropulsionCode	""
10	SQLCHAR	0	1	""	10	strWheelplanCode	""
11	SQLCHAR	0	2	""	11	strTaxCode	""
12	SQLCHAR	0	1	""	12	strCurrentPeriod	""
13	SQLCHAR	0	1	""	13	strPreviousPeriod	""
14	SQLCHAR	0	8	""	14	strFirstRegistrationDate	""
15	SQLCHAR	0	8	""	15	strDateExported	""
16	SQLCHAR	0	8	""	16	strScrappageDate	""
17	SQLCHAR	0	8	""	17	strDateManufactured	""
18	SQLCHAR	0	1	""	18	blnCherishedInd	""
19	SQLCHAR	0	1	""	19	blnImportedInd	""
20	SQLCHAR	0	1	""	20	blnKitInd	""
21	SQLCHAR	0	1	""	21	blnRebuiltInd	""
22	SQLCHAR	0	1	""	22	strCurrKeeperTitleCode	""
23	SQLCHAR	0	5	""	23	strCurrKeeperPostcode	""
24	SQLCHAR	0	8	""	24	strCurrKeeperAcquisitionDate	""
25	SQLCHAR	0	1	""	25	strPrevKeeperTitleCode	""
26	SQLCHAR	0	5	""	26	strPreviousKeeperPostcode	""
27	SQLCHAR	0	8	""	27	strPrevKeeperAcquisitionDate	""
28	SQLCHAR	0	8	""	28	strPrevKeeperDisposalDate	""
29	SQLCHAR	0	2	""	29	strNumberPreviousKeepers	""
30	SQLCHAR	0	5	"\n"	30	strGrossWeight	""

I have tried to import in DTS - using the wizard - but I can't get it to work because there is the weird "nul" character in the first column and the wizard does not reconise it.

For small datasets I import to varchar then update convert no problem, but I am a bit stuck because of the quantity here.

Many thanks for your reply.

Michael

 

[AlexCuse]

Hmm, I think you may have to bring it in to a temp table and then insert to your final table (where it is a date/time).

You could use something like this:

right(DATECOL, 4) + substring(DATECOL, 3,2) + left(DATECOL, 2)

to get the column into yyyymmdd when you do the final insert. You'll probably want to do this insert in chunks as well if its' 37 million rows.

HOpe this helps,

Alex

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

 

[micang]

Hi Alex,

Thanks, I think you're right, I will probably have to do it in chunks - safest route I think.

Thanks for your help.

Michael

 

[ESquared]

you could try

set dateformat dmy

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.

_{The best part about anything that has cheese is the cheese.}[/color]

 

[gmmastros]

DateFormat is only useful if there is a separator between the various components of the date.

Ex:

[tt][blue]
Set DateFormat dmy

Select Convert(DateTime, '22-10-2007')
Select Convert(DateTime, '22/10/2007')
Select Convert(DateTime, '22102007') [green]-- Out of range error.[/green]
[/blue][/tt]

When converting to DateTime, and you have 8 characters that are all numbers (without any seperator), it will ALWAYS be interpreted as YYYYMMDD.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ESquared]

I did not know that. Thanks for pointing it out, George.

 

[micang]

Hi all,

Some of the date fields have blanks when imported, when I insert these into a new table as datetime, it converts these to 1901-01-01 - but I noticed that if it's null it doesnt convert - which is what I would like.

Is there away on the insert to convert dates as dates and the blanks as nulls?

Thanks.

Michael

 

[AlexCuse]

Hey Michael - take a look here:

declare @t table (n varchar(8))

insert @t
select '20070914'
union all select null
union all select '00000000'

select cast(nullif(n, '00000000') as datetime) from @t

the nullif(n, '00000000') does sort of a 'reverse coalesce' and replaces any '00000000' rows with NULL.

Hope this helps,

Alex

[small]----signature below----[/small]
Who are you, and why should I care?
Side A: We're a community of outdated robots who refused to upgrade and came here
*changes sides*
Side B: to live a simpler existence. Free of technology.

 

[micang]

Hi Alex,

Thanks so much, real helpful - did not know that.

I wonder if I should create new column and "rearrange" the date i.e. - '31012006' to '20063101' and then just insert that to a new table with datetime format, cuase then from what I understand it should convert it no problem.

I guess it's 6 of one and half-a-dozen of the other?

Nonetheless, with the help from you and everyone, I can now get this done.

Appreciated.

Michael

 

[ESquared]

er, it would be '20060131'

 

[micang]

Thanks Esquared!! Good spotting!