Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Converting a string into a date

Posted on 2004-04-13
9
Medium Priority
?
1,494 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 70

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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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 1500 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 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