Insert date value into SQL Server datetime columm

sbass1 · Post by **sbass1** » Wed Feb 25, 2009 1:30 am

[--- 05Mar09 - If you've found this post via search, and don't want to read the long thread, scroll to the end for details of the resolution ---]

(Sorry, I know this is a FAQ, I did search before posting...)

Job layout:

Seq File --> xfm --> DRS stage, SQL Server table

DDL and test inserts via MS SQL Server Management Studio:

USE [blah]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ScottDateTesting](
[Num] [int] NULL,
[EffectiveDate] [datetime] NULL
) ON [PRIMARY]

DELETE FROM dbo.ScottDateTesting
GO
INSERT INTO dbo.ScottDateTesting (Num,EffectiveDate) VALUES (1,'16-FEB-09')
INSERT INTO dbo.ScottDateTesting (Num,EffectiveDate) VALUES (2,'2009-16-02')
INSERT INTO dbo.ScottDateTesting (Num,EffectiveDate) VALUES (3,'16-FEB-09 12:34:56')
INSERT INTO dbo.ScottDateTesting (Num,EffectiveDate) VALUES (4,'2009-16-02 12:34:56')
INSERT INTO dbo.ScottDateTesting (Num,EffectiveDate) VALUES (5,'2009-02-16') /* does not work */
INSERT INTO dbo.ScottDateTesting (Num,EffectiveDate) VALUES (6,'20091602') /* does not work */
INSERT INTO dbo.ScottDateTesting (Num,EffectiveDate) VALUES (7,'2009 16 02') /* does not work */
GO
SELECT * FROM dbo.ScottDateTesting

What works:
Leave EffectiveDate metadata datatype=Timestamp
This generates the SQL code:

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES (TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'))

After a bit of hacking, I've deduced that TO_DATE must be an internal DataStage function that converts the string to the proper format for the target database. TO_DATE isn't a SQL Server function, although I've used it before on Oracle projects.

What doesn't work:
Change EffectiveDate metadata datatype=Date (my input data only has date data, but SQL Server doesn't have a date datatype, only datetime). So my metadata is now out of sync with my physical data in an attempt to generate SQL code that works.

This generates the SQL code:

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES (?)

1) I'm wondering why the DRS stage doesn't generate the code:

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES (TO_DATE(?, 'YYYY-MM-DD'))

(I swear I had some combination once that generated this code, but I can't seem to duplicate it again...)

2) I've used various permutations of oconv to format the string to values that worked when I submitted them via the SQL Server client. They all failed. Here are excerpts from the DS job log:

ScottTestingTimestamp..xfm: At row 1, link "Load", while processing column "EffectiveDate"
Value treated as NULL
Attempt to convert String value "2009-16-02" to Date type unsuccessful

ScottTestingTimestamp..xfm: At row 1, link "Load", while processing column "EffectiveDate"
Value treated as NULL
Attempt to convert String value "16-FEB-09" to Date type unsuccessful

ScottTestingTimestamp..xfm: At row 1, link "Load", while processing column "EffectiveDate"
Value treated as NULL
Attempt to convert String value "16 FEB 09" to Date type unsuccessful

ScottTestingTimestamp..xfm: At row 1, link "Load", while processing column "EffectiveDate"
Value treated as NULL
Attempt to convert String value "2009-02-16" to Date type unsuccessful

One last thought I had is that maybe my quote character is not set properly for the target table, so that what looks like "2009-16-02" in the job log wasn't the literal string '2009-16-02' (including single quotes) passed to SQL Server. But, in the DRS stage, I don't see where this is set (unlike the ODBC stage).

Thanks,
Scott

DS_SUPPORT · Post by **DS_SUPPORT** » Wed Feb 25, 2009 4:55 am

Change the Effectivedate in your DRS stage to Timestamp, and make sure you are passing the input in the format 'YYYY-MM-DD HH24:MI:SS', even , if you dont have time part also append it with 00:00:00.

Convert your incoming date from the sequential file to the above specified format, using OCONV.

chulett · Post by **chulett** » Wed Feb 25, 2009 8:30 am

That sounds like Oracle advice.

ray.wurlod · Post by **ray.wurlod** » Wed Feb 25, 2009 3:08 pm

Have you tried using an internal format date?

Have you tried using an internal format date with the Data Element set to Date?

sbass1 · Post by **sbass1** » Wed Feb 25, 2009 4:14 pm

ray.wurlod wrote:(1) Have you tried using an internal format date?

(2) Have you tried using an internal format date with the Data Element set to Date? ...

(1) You mean something like iconv("2009-02-25","DYMD") == 15032? Nope. If SQL Server accepts

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES ('2009-25-02')

from the SQL Server client, I'm not sure why I'd be expected to try

INSERT INTO dbo.ScottDateTesting (Num,EffectiveDate) VALUES (15032) ???

(2) Sorry not sure what you mean here??? Isn't this asking the same thing as #1?

Again...I know how to get this to work - use timestamp metadata and timestamp string format.

What I'm wanting to know is:

1) Why doesn't the DRS stage create TO_DATE(?,'YYYY-MM-DD') code if the output column type is DATE?

2) If

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES ('2009-25-02')

works in SQL Server, then why doesn't

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES (?)

work when the incoming data is definitely 2009-25-02?

Kryt0n · Post by **Kryt0n** » Wed Feb 25, 2009 4:34 pm

sbass1 wrote:What I'm wanting to know is:

1) Why doesn't the DRS stage create TO_DATE(?,'YYYY-MM-DD') code if the output column type is DATE?

2) If

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES ('2009-25-02')

works in SQL Server, then why doesn't

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES (?)

work when the incoming data is definitely 2009-25-02?

Maybe I am missing something but
TO_DATE(?,'YYYY-MM-DD')

does not match

2009-25-02

Unless you are on Saturn...

That aside, is your DataStage date format the same as your SQL Server format? Particularly if your DB is expecting the format YYYY-DD-MM...[/b]

sbass1 · Post by **sbass1** » Wed Feb 25, 2009 4:53 pm

Kryt0n wrote:
sbass1 wrote:What I'm wanting to know is:

1) Why doesn't the DRS stage create TO_DATE(?,'YYYY-MM-DD') code if the output column type is DATE?

2) If

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES ('2009-25-02')

works in SQL Server, then why doesn't

INSERT INTO dbo.ScottDateTesting (EffectiveDate) VALUES (?)

work when the incoming data is definitely 2009-25-02?
Maybe I am missing something but
TO_DATE(?,'YYYY-MM-DD')

does not match

2009-25-02

Unless you are on Saturn...

That aside, is your DataStage date format the same as your SQL Server format? Particularly if your DB is expecting the format YYYY-DD-MM...[/b]

Yes you're missing something:

1) Why doesn't the DRS stage create TO_DATE(?,'YYYY-MM-DD') code if the output column type is DATE?

was just an example. The date format string could be anything; the fact is the TO_DATE function call is not generated in the code.

And since that code is NOT generated, it doesn't apply to my second question, which is why a syntactically correct SQL Server insert statement fails in DS. (Please re-read point #2 above carefully...)

Mike · Post by **Mike** » Wed Feb 25, 2009 6:07 pm

why a syntactically correct SQL Server insert statement fails in DS

Working with SQL Server from UNIX utilizes ODBC, so I'd look at the capabilities/limitations inherent in the ODBC driver for SQL Server.

Sorry, I haven't worked with SQL Server ODBC so I can't offer any advice there.

Mike

sbass1 · Post by **sbass1** » Wed Feb 25, 2009 7:18 pm

Mike wrote:
why a syntactically correct SQL Server insert statement fails in DS
Working with SQL Server from UNIX utilizes ODBC, so I'd look at the capabilities/limitations inherent in the ODBC driver for SQL Server.
Mike

Does it matter that I'm using the DRS stage and not the ODBC stage for output? I thought the DRS stage used internal DS drivers specific to each database, rather than generic ODBC drivers.

sbass1 · Post by **sbass1** » Wed Feb 25, 2009 7:19 pm

Mike wrote:
why a syntactically correct SQL Server insert statement fails in DS
Working with SQL Server from UNIX utilizes ODBC, so I'd look at the capabilities/limitations inherent in the ODBC driver for SQL Server.
Mike

Does it matter that I'm using the DRS stage and not the ODBC stage for output? I thought the DRS stage used internal DS drivers specific to each database, rather than generic ODBC drivers.

Mike · Post by **Mike** » Wed Feb 25, 2009 8:21 pm

My understanding is that the DRS stage uses native database interfaces... and there is no native SQL Server interface on UNIX.

Mike

ray.wurlod · Post by **ray.wurlod** » Wed Feb 25, 2009 8:23 pm

DRS can also use ODBC protocol.

A UNIX-based ODBC driver for SQL Server ships with DataStage.

Mike · Post by **Mike** » Wed Feb 25, 2009 8:32 pm

Which means that even though you're using the DRS stage, you are probably still talking to SQL Server through an ODBC interface.

Mike

Kryt0n · Post by **Kryt0n** » Wed Feb 25, 2009 8:46 pm

Could the Sybase stages be configured to access SQL Server?

ray.wurlod · Post by **ray.wurlod** » Wed Feb 25, 2009 8:47 pm

Which should mean that, by setting the Data Element to Date, you can get the internal format date automatically converted by the BCI (BASIC SQL Call Interface) through which the ODBC driver is invoked.