November 27, 2014 at 2:52 am
All of the suggestions so far with converts and udfs are fine, but I would approach it by building a conversion table
CREATE TABLE dbo.DateConvert(
DB2Date AS VARCHAR(10),
CalendarDate AS DATE
)
INSERT INTO DateConvert(DB2Date,CalendarDate) VALUES ('0010101','1900-01-01')
and then insert all the possible reference dates; even if you are looking at 3 centuries, this is only 11,000 records in the reference table.
In every query where you want to convert the date, simply include the reference table in a JOIN. If you index the DB2Date then the lookups will be extremely quick.
November 27, 2014 at 9:18 am
aaron.reese (11/27/2014)
All of the suggestions so far with converts and udfs are fine, but I would approach it by building a conversion table
CREATE TABLE dbo.DateConvert(
DB2Date AS VARCHAR(10),
CalendarDate AS DATE
)
INSERT INTO DateConvert(DB2Date,CalendarDate) VALUES ('0010101','1900-01-01')
and then insert all the possible reference dates; even if you are looking at 3 centuries, this is only 11,000 records in the reference table.
In every query where you want to convert the date, simply include the reference table in a JOIN. If you index the DB2Date then the lookups will be extremely quick.
I'd need to test this approach, but I had a bad experience with a join like this and the query ran 7 times slower with a reference table. I'm not sure how you did the math, I think you missed a zero in the number of rows.
However, this is an approach to populate the reference table from 1900-01-01 to 2100-01-01.
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E6(N) AS (SELECT 1 FROM E2 a, E2 b, E2 c), --10E+6 or 1,000,000 rows max
cteTally(N) AS (SELECT TOP (73050) --Until 2100-01-01
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1
FROM E6
)
SELECT RIGHT( CONVERT(char(8), DATEADD( dd, N, 0), 112) - 9000000, 7) DB2Date,
DATEADD( dd, N, 0) CalendarDate
FROM cteTally;
November 27, 2014 at 9:23 am
my mental arithmetic was always suspect - that's why i use a computer!
November 27, 2014 at 9:25 am
Would that approach really be worth it for what sounds like a one time job?
December 1, 2014 at 8:50 am
I love the function of just adding values together, though I feel uneasy that it might not work in 2100 and beyond. Course, not sure we need to plan on this code lasting another 86 years.
December 9, 2014 at 12:57 am
Thanks guys for the reply's
Thanks Jason...The one with Varchar is working for me. But In some of the Columns we have '0' for which the conversion is failing. Can you please suggest how do i handle those.
CREATE FUNCTION [dbo].[Convert_Date] (@date_column VARCHAR(20))
Returns date as begin
declare @date date
set @date = CONVERT(DATE,CASE WHEN LEFT(@date_column,1) = '1' THEN '20'
ELSE '19' END + RIGHT(@date_column,6), 112)
RETURN @date
END
December 9, 2014 at 12:35 pm
Have '0' as in just '0' for the date, or 0 in front of the date.
Can you provide some sample data of problematic rows?
December 9, 2014 at 12:40 pm
Should I insist on this method? I just added a NULLIF function and it works.
SELECT CAST( CAST( 19000000 + NULLIF(oldDate, 0) AS CHAR(8)) AS date)
FROM( VALUES('0980412'),('1100323'), ('0'))x(oldDate)
December 9, 2014 at 12:50 pm
Just throw in a check for whatever validation you need to do.
CREATE FUNCTION [dbo].[Convert_Date] (@date_column VARCHAR(20))
Returns date as begin
declare @date date
IF LEN(@date_column) = 7
BEGIN
set @date = CONVERT(DATE,CASE WHEN LEFT(@date_column,1) = '1' THEN '20'
ELSE '19' END + RIGHT(@date_column,6), 112)
END
RETURN @date
END
December 10, 2014 at 12:57 am
Hi Zzartin...
I am still getting the error
'Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.'
the column has below sample records :
940930
941027
941205
950117
0
1001213
1001214
0
1141128
1141129
1141130
Thanks...
December 10, 2014 at 7:35 am
Validating the length should remove all the bad records from the sample posted.
December 16, 2014 at 5:20 pm
Luis Cazares (12/9/2014)
Should I insist on this method? I just added a NULLIF function and it works.
SELECT CAST( CAST( 19000000 + NULLIF(oldDate, 0) AS CHAR(8)) AS date)
FROM( VALUES('0980412'),('1100323'), ('0'))x(oldDate)
I think Luis's logic would work fine in your function. Just beware a data value that is not really a date such as could happen for an invalid leap year (example 2013-02-29).
The following will fail since it is not a date
select cast('2013-02-29' as date)
----------------------------------------------------
December 18, 2014 at 5:47 pm
How about if you just stuff it?
WITH LegacyData (LegacyDate) as
(
SELECT '0980412' UNION ALL
SELECT '1100323' UNION ALL
SELECT '940930' UNION ALL
SELECT '941027' UNION ALL
SELECT '941205' UNION ALL
SELECT '950117' UNION ALL
SELECT '0' UNION ALL
SELECT '1001213' UNION ALL
SELECT '1001214' UNION ALL
SELECT '0' UNION ALL
SELECT '1141128' UNION ALL
SELECT '1141129' UNION ALL
SELECT '1141130'
)
SELECT a.LegacyDate
,NewDate=CASE
WHEN b.LegacyDate <> '0000000'
THEN CAST(STUFF(b.LegacyDate, 1, 1, 19+LEFT(b.LegacyDate,1)) AS DATE)
END
FROM LegacyData a
CROSS APPLY (SELECT RIGHT(10000000+LegacyDate, 7)) b (LegacyDate);
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 19, 2014 at 11:30 am
CELKO (12/19/2014)
We are migrating data from old DB2 systems to sql server 2012, the DATE FORMAT in those systems is in decimal format with 7 digits. CYYMMDD format. I need to convert this into DD/MM/YYYY format.
In SQL, dates are not formatted. They have some unknown internal representation and we do not care as long as the SQL engine can use that format. Think of floating numbers. Your mind set is still back in COBOL, where the format as a string was the storage model!
Since SQL is a declarative database language, we do not like to create procedures and certainly not functions that cannot be optimized. Instead, get a spreadsheet and build a look-up table for 100+ years. It will be small and have only two columns,(DB2_date DECIMAL (7,0) NOT NULL PRIMARY KEY, sql_date date NOT NULL). 36525 rows will fit into main storage on a desktop machine.
If there are other tables in DB2 system that also have a date column in this format then they all need to be converted to a datetime (or DATE datatype) for consistency . About the only drawback would be re doing any prior queries/functions that did some kind of string manipulation to simulate date arithmetic. But that is kind of like saying you need to get new pants sizes after loosing excess weight, not bad in the overall scheme of things. Store your date representation as a date type in sql server. How are you planning on moving the data? SSIS? Do you have a odbc provider for the db2 system somewhere? This will indicate your detailed implementation.
----------------------------------------------------
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply