Importing date into SQL - 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

Import/Export (DTS) and Replication (2000)

Importing date into SQL

Author

Topic

R2D2RABEAU
Starting Member

13 Posts

Posted - 2006-11-20 : 05:53:03

I have dates in the following format [20061025] in excel and I am trying to import it as in a SQL table column like dateime or smalldatetime but the only way to get error messages is to set the datatype to [int]. This not good for me, can you think of a way?
(i am new at this...)

Thank you for your help

P.O.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-20 : 06:06:28

Yes. Excel thinks 20061025 is not a date. It think the content is a number (20 million 61 thousand and 25). It is the human mind that interprets the content visually as a date. You have to break down the number with some kind of formula, such as

declare @i int

select @i = 20061025

select @i, DATEADD(day, @i % 100 - 1, DATEADD(month, @i % 10000 / 100 - 1, DATEADD(year, @i / 10000- 1900, 0)))

Peter Larsson
Helsingborg, Sweden

R2D2RABEAU
Starting Member

13 Posts

Posted - 2006-11-20 : 06:27:49

quote:
Originally posted by Peso

Yes. Excel thinks 20061025 is not a date. It think the content is a number (20 million 61 thousand and 25). It is the human mind that interprets the content visually as a date. You have to break down the number with some kind of formula, such as
declare @i int

select @i = 20061025

select @i, DATEADD(day, @i % 100 - 1, DATEADD(month, @i % 10000 / 100 - 1, DATEADD(year, @i / 10000- 1900, 0)))

Peter Larsson
Helsingborg, Sweden

Thank you for your help,

How can I generalise that formula so that when I bulk import data the dates is converted when imported?

P.O.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-20 : 06:45:01

DTS or SSIS?
In SSIS use a calculated column to convert to INT to DATETIME with the formula above.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-20 : 07:06:35

This is little simplified (not using minus 1 two times)

declare @i int

select @i = 20061026

select @i, DATEADD(day, @i % 100, DATEADD(month, @i % 10000 / 100, DATEADD(year, @i / 10000 - 1900, -32)))

Peter Larsson
Helsingborg, Sweden

R2D2RABEAU
Starting Member

13 Posts

Posted - 2006-11-20 : 07:28:11

quote:
Originally posted by Peso

DTS or SSIS?
In SSIS use a calculated column to convert to INT to DATETIME with the formula above.

Peter Larsson
Helsingborg, Sweden

What about DTS (I use DTS, SQL server2000)?

P.O.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-20 : 08:16:33

Haven't done much work in DTS lately.
A workaround could be to import all data in a temporary table, add a new column and use the formula above,
and then export it all to the destination.

Peter Larsson
Helsingborg, Sweden

R2D2RABEAU
Starting Member

13 Posts

Posted - 2006-11-20 : 08:52:53

Thank you for all your help, much appreciated!

P.O.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources