Solved

Converting a string into a date

Posted on 2004-04-13
9
1,473 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: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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

790 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