Insert date value into SQL Server datetime columm
Posted: 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
(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