Solved

Converting a string into a date

Posted on 2004-04-13
9
1,446 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
9 Comments
 
LVL 69

Expert Comment

by:ScottPletcher
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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now