DTS Transformations - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

DTS Transformations

Author

Topic

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-11-17 : 10:45:11

Hi everyone

I am having a problem transforming dates.
My source is a text file.
When I transform the dates I use the DTS DateTime String
my source column is "yyyyMMddHHmmss"
my destination is "dddd, MMMM dd, yyyy HH:mm:ss"

Fine this works, until I reach a null value.
When I created the table I allowed null values in this column, but when I run the package I get an error whenever it encounters a null value.

TransformDateTimeString 'DTSTransformation_9',column pair 1(source column 'Col017'(DBTYPE_STR),destination column 'Weighed'(DBTYPE_STR)):Cannot convert datetime to destination.

I am not using the ActiveX Scripting for this, as I am not too familiar with it yet,

Any help would be appreciated

Thanks

Mufasa

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 12:27:28

You could use a staging table that has VARCHAR as the data type that way you get your data into SQL Server. Then copy your data from the staging table to the destination table using T-SQL.

Tara

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-11-17 : 14:52:24

I tried that, but now when I import it at char (14), when the column is blank, it inserts 14 spaces. So now when I try to convert to datetime, I keep getting errors.
my data for that column is 20031117145103.

I even tried to import as numeric, but the spaces still became a problem.

Mufasa

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 14:53:18

Why use CHAR(14)? Use VARCHAR instead.

Tara

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-11-17 : 15:17:00

You are right about the varchar, but I still end up with the same problem
I tried using this line
NULLIF (CONVERT (datetime, dbo.bxh2.EDate),' ')

Even when I use varchar it still brings in a 'space' character

I am using vutil32 to create my sourse file.
I have done the nullif covert before on another file created with vutil32 without a problem.
I think the probelm is that I am importing date & time within the same column.(Which I need)
That is the only difference I can see from other imports that I have done.

Mufasa

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 15:22:43

You need ISNULL instead of NULLIF.

Can't vutil32 create a different date and time format?

BTW, you can remove trailing blanks with RTRIM.

Tara

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-11-17 : 16:01:20

The vutil32 converts cobol database into plain text, I then have to manually create the columns and their data types in SQL.
As far I am know, that is all Vutil32 can do for what I need.

'ISNULL(CONVERT (datetime, RTRIM(Weighed)), 0)'the data in weighed is 20031117160324'
Using isnull or nullif after rtrim, I still get the error
"Syntax error converting datetime from character string"

Mufasa

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-17 : 16:09:28

That's because it's not a datetime format

SELECT ISDATE('20031117160324')

Brett

8-)

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-11-17 : 16:17:14

Hi Brett

I know that 'weighed' is not a date format, I am trying to change it into one.
I have done it before without the time value and it has worked.

Mufasa

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 16:18:33

[code]

DECLARE @String VARCHAR(50)
DECLARE @Date DATETIME

SET @String = '20031117160324'
SET @Date = CONVERT(VARCHAR(50), SUBSTRING(@String, 5, 2)) + '/' + CONVERT(VARCHAR(50), SUBSTRING(@String, 7, 2)) + '/' + CONVERT(VARCHAR(50), SUBSTRING(@String, 1, 4)) + ' ' + CONVERT(VARCHAR(50), SUBSTRING(@String, 9, 2)) + ':' + CONVERT(VARCHAR(50), SUBSTRING(@String, 11, 2)) + ':' + CONVERT(VARCHAR(50), SUBSTRING(@String, 13, 2))

PRINT @Date

[/code]

Above is code that turns the string into one possible proper datetime format.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-17 : 16:40:55

[code]
CREATE FUNCTION udf_TO_DATE (@x varchar(14))
RETURNS datetime
AS
BEGIN
Return CONVERT(datetime,SUBSTRING(@x,1,4)+'/'+SUBSTRING(@x,5,2)+'/'+SUBSTRING(@x,7,2)+ ' '
+SUBSTRING(@x,9,2)+ ':'+SUBSTRING(@x,11,2)+ ':'+SUBSTRING(@x,13,2),120)
END
GO

DECLARE @x varchar(14)
SELECT @x = '20031117160324'
SELECT ISDATE(@x), ISDATE(SUBSTRING(@x,1,4)+'/'+SUBSTRING(@x,5,2)+'/'+SUBSTRING(@x,7,2)+ ' '
+SUBSTRING(@x,9,2)+ ':'+SUBSTRING(@x,11,2)+ ':'+SUBSTRING(@x,13,2))

SELECT CONVERT(datetime,SUBSTRING(@x,1,4)+'/'+SUBSTRING(@x,5,2)+'/'+SUBSTRING(@x,7,2)+ ' '
+SUBSTRING(@x,9,2)+ ':'+SUBSTRING(@x,11,2)+ ':'+SUBSTRING(@x,13,2),120)

SELECT dbo.udf_TO_DATE(@x)

[/code]

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 16:43:07

And there you have it! mufasa you can call Brett's UDF to convert your string into proper datetime format. You can use the UDF in a SELECT statement, so it should be pretty easy.

Tara

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-11-17 : 16:51:47

Hi Tara & Brett
You guys/gals are leagues ahead of me when it comes to SQL

That works fine when there is a value in 'Weighed', but when there is not, it returns an error.
What I don't understand is that in my DTS package, the first column that I pull in, I use the transformation:
{DateTime String
my source column is "yyyyMMddHHmmss"
my destination is "dddd, MMMM dd, yyyy HH:mm:ss"}
This works great, but every row has a date in the first column.
All I want to do, is be able to return a null value when there is not a date, and transform like I did in the first column when there is.

FYI
My table that I am importing is a text file created from a cobol database file using Vutil32
My table has 25 columns with various data types (char, int, datetime)
There are apx 25k records

I really apprieciate the time and effort you guys are putting into this problem.

Mufasa

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 16:56:31

What is this format: dddd, MMMM dd, yyyy HH:mm:ss? That is not a valid DATETIME format. Could you provide the table structure of the destination table (in the form of CREATE TABLE statement) so that we can play around with it on our end?

Are you using a simple copy column transformation? If you aren't, then what are you using?

Show us a sample row that is having a problem. Perhaps we can figure it out faster that way.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-17 : 16:57:17

Just need to add some error checking in case you don't have the correct format...

Something like:


CREATE FUNCTION udf_TO_DATE (@x varchar(14))
RETURNS datetime
AS
BEGIN

IF (SELECT ISDATE(SUBSTRING(@x,1,4)+'/'+SUBSTRING(@x,5,2)+'/'+SUBSTRING(@x,7,2)+ ' '
	+SUBSTRING(@x,9,2)+ ':'+SUBSTRING(@x,11,2)+ ':'+SUBSTRING(@x,13,2))) = 1

	Return CONVERT(datetime,SUBSTRING(@x,1,4)+'/'+SUBSTRING(@x,5,2)+'/'+SUBSTRING(@x,7,2)+ ' '
			+SUBSTRING(@x,9,2)+ ':'+SUBSTRING(@x,11,2)+ ':'+SUBSTRING(@x,13,2),120)
  ELSE 
	Return Null
END
GO

But I'm getting an error...

It says...

quote:

Server: Msg 455, Level 16, State 2, Procedure udf_TO_DATE, Line 65535
The last statement included within a function must be a return statement.

Don't know why it's being picky..you can always assign it to a variable..

DECLARE @y
IF condition ...SELECT @y = conversion
ELSE SELECT @y = NULL

RETURN @y

EDIT: You must have been on the coconut telegraph...how did I know you were going to need that...~~can you fix it up yourself?~~

Never mind...Tara did it for you..

And your "table" doesn't sound very table like to me...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 17:01:13

quote:
Originally posted by X002548

But I'm getting an error...

It says...

[quote]
Server: Msg 455, Level 16, State 2, Procedure udf_TO_DATE, Line 65535
The last statement included within a function must be a return statement.

I ran into this problem before. I ended up putting the result within the IF statement into another variable. I then returned this variable at the end. Something like this:



CREATE FUNCTION udf_TO_DATE (@x varchar(14))
RETURNS datetime
AS
BEGIN

DECLARE @Date datetime

IF (SELECT ISDATE(SUBSTRING(@x,1,4)+'/'+SUBSTRING(@x,5,2)+'/'+SUBSTRING(@x,7,2)+ ' '
	+SUBSTRING(@x,9,2)+ ':'+SUBSTRING(@x,11,2)+ ':'+SUBSTRING(@x,13,2))) = 1

	SET @Date =  CONVERT(datetime,SUBSTRING(@x,1,4)+'/'+SUBSTRING(@x,5,2)+'/'+SUBSTRING(@x,7,2)+ ' '
			+SUBSTRING(@x,9,2)+ ':'+SUBSTRING(@x,11,2)+ ':'+SUBSTRING(@x,13,2),120)
  ELSE 
	SET @Date = Null

RETURN @Date

END
GO

Tara

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-11-17 : 17:06:02

I will create a synapse(What I Did) in Word with screen shots and send via e-mail tonight.

Thanks

Mufasa

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-17 : 17:10:30

quote:
Originally posted by mufasa

I will create a synapse(What I Did) in Word with screen shots and send via e-mail tonight.

Please just answer these:

What is this format: dddd, MMMM dd, yyyy HH:mm:ss? That is not a valid DATETIME format and will not be able to be imported into a DATETIME column.

Could you provide the table structure of the destination table (in the form of CREATE TABLE statement) so that we can play around with it on our end?

Also providing a couple of sample rows would greatly help out.

Tara

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-11-18 : 09:31:07

This format is from the tranformation menu when you select Datetime String in the package creation.

I am using SQL 2K and it is valid, meaning it does work.

In the create table statement, the format I am using is simple datetime for the columns that work and char(#) for the columns that have blank dates. below is the create table statement.

CREATE TABLE [CCTD].[dbo].[bxh2] (
[Box#] char (10) NULL,
[Store] char (3) NULL,
[Pin#] char (12) NULL,
[Transport] char (2) NULL,
[Unicode] char (9) NULL,
[BoxOpen] datetime NULL,
[EmpOP] char (6) NULL,
[BoxClose] datetime NULL,
[EmpCL] char (6) NULL,
[BxQty] int NULL,
[weight] numeric (16,2) NULL,
[Weighed] varchar (14) NULL,
[EmpWt] char (6) NULL,
[Rec'd] char (14) NULL,
[RecBy] char (6) NULL,
[Confirm] char (14) NULL,
[ConfirmBy] char (6) NULL,
[QtyRec] int NULL,
[QtyDif] int NULL,
)

Here is a sample row
0009350303 042 MEX042863193 OW K1N40 YOW 08/10/2003 9:54:03 AM ALFRC 08/10/2003 10:15:21 AM ALFRC 3 2 20031008120822 FEGUF 20031014131330 042161 20031014132022 042161 3 0

Thanks Mufasa

mufasa
Yak Posting Veteran

62 Posts

Posted - 2003-11-18 : 10:02:30

STOP THE PRESSES

udf_TO_DATE ---- GRREEAATTTT!!!!!!!!!

Thank-You so much, made my life a lot easier

You guys/gals are great!!

Mufasa ;-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-18 : 10:10:18

quote:
Originally posted by mufasa

STOP THE PRESSES

udf_TO_DATE ---- GRREEAATTTT!!!!!!!!!

Thank-You so much, made my life a lot easier

You guys/gals are great!!

Mufasa ;-)

It's a little Oracle-ism....

Glad it works for you...

Now...why is your data such a mess? Importing an Excel spreadsheet (another guess)?

Brett

8-)

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources