Solved

Converting a string into a date

Posted on 2004-04-13
9
1,484 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

737 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