Solved

Converting a string into a date

Posted on 2004-04-13
9
1,488 Views
Last Modified: 2008-01-09
I want to import some data into my MS SQL server thought DTS.

I have all the filed import ok apart from the date feild as it is in a string format IE 20040408 which relates back to YYYYDDMM. how can i convert this date into a format a format that can be imported into my TimeDate field?

Here is the SQL that i have created for the import so far.

CREATE TABLE [IPS].[dbo].[RevByyear] (
[YEAR_NBR] varchar (4) NULL,
[RES_AGENT_CD] varchar (30) NULL,
[INVOICE_AMT] money NULL,
[RANK_NBR] int NULL,
[PROCESS_DAT] datetime NULL
)



Willa
0
Comment
Question by:willa666
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 10816497
That value should insert just fine into a datetime column.  You must have some bad data somewhere.  You could try loading into a staging table that defines that column as char/varchar, then check for invalid dates:

SELECT *
FROM stagingTable
WHERE ISDATE(process_dat) <> 1
0
 
LVL 1

Author Comment

by:willa666
ID: 10816530
So i should be able to import the data from a text file straight off?

Should their not be a convert staement intheir somewhere?
0
 
LVL 1

Author Comment

by:willa666
ID: 10816701
I get a message when i import saying TransformCopy 'DirectCopyXForm' conversion error: Convertion invaild for datatypes on coloum pair 5 (sourcecolumn 'Col005'(DBTYPE_STR), destination column 'PROCESS_DAT' (DBTIMESTAMP)).

so i am assuming that i can just import the date from my test file straight in without using a convert command somewhere. here are the first 2 line of the TXT file.

2004,MMUSE,205590.64,1,20040409,20040408
2004,MDIKE,162266.26,2,20040409,20040408

So i dont think their can be a problem with the data that i am importing, is their?

Willa
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 7

Expert Comment

by:ChrisFretwell
ID: 10817625
If you're doing this from within DTS, you can specify what you expect the date format to be (and it sounds like you need to)
To do this, in the transformation tab, select the source and destination field, select new, then datetime string. Thjen select the properties button. In here, you can specify what the import date format will look like. In your case YYYYMMDD (make sure you use upper case MM because lower case is minutes)

chris
0
 
LVL 1

Author Comment

by:willa666
ID: 10834705
Chris that sound great mate.

I have followed your steps and i get stuck at the part where  i need to click on the properties button. i dont have a properties button on that window. Are you sure this is correct?
0
 
LVL 7

Accepted Solution

by:
ChrisFretwell earned 500 total points
ID: 10834766
When you are in the transformation tab, do you already have a mapping between the 2 fields? If so, you need to delete it. Its probably just a straight copy which wont have the properties button. After deleting the existing copy (just for the date fields if you can), select the source and destination and then pick up from above.
If it still doesnt work, let me know and I'll see if I can go slower through it.

chris
0
 
LVL 1

Author Comment

by:willa666
ID: 10835329
Right lets start from the beginning

I am using a MS SQL 2000 server to import my file CSV file into. I am importing the file with DTS import/export wizard. I select the data soucre and the data destination. then in the SELECT TABLES AND VIEWS i select the transform button at the end of the row for my import job. this takes me into COLUMN MAPPINGS AND TRANSFORMATIONS.

I cant delete any of the items that are in this window!

are we usingthe smae program or is their differnt versions?

:)

Willa
0
 
LVL 1

Author Comment

by:willa666
ID: 10837812
Has no one else had this issue come up?

or does it just work for everyone else!

:)
0
 

Expert Comment

by:kyle747
ID: 11154344
the problem is that the table already exists (you created it when you tried to import previously)
delete it and try again
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question