November 19, 2014 at 1:32 pm
Hi ..
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.
Can any body help in writing a procedure for this, as we have this scenario in lot of tables.
Thanks in advance.
November 19, 2014 at 1:43 pm
sqlquery29 (11/19/2014)
Hi ..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.
Can any body help in writing a procedure for this, as we have this scenario in lot of tables.
Thanks in advance.
How about you move to a date datatype instead of storing dates as some other "format". This is what that datatype is designed for. What do your existing values actually look like?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 19, 2014 at 2:16 pm
We need it in Date format.
The dates in DB2 look like '0980412', '1100323' where the first digit represents Century, '0' for 1900 and '1' for 2000.
'0980412' - 12 APRIL 1998
'1100323' - 23 MARCH 2010
November 19, 2014 at 2:21 pm
I'd probably CASE a SUBSTRING of the first character to determine the century. With that, you can add the next digits to get the year (2000 + 14) with another substring. Then it's a matter of extracting the month and date.
Use DATEFROMPARTS to build the date. http://msdn.microsoft.com/en-us/library/hh213228.aspx
Are you converting this data inline, using it in a query as a return or something else? You could easily do this with an inline table valued function, passing in the original value.
November 19, 2014 at 2:24 pm
SELECT CONVERT(datetime, CASE WHEN LEFT('1100323', 1) = '1' THEN '20' ELSE '19' END + RIGHT('1100323', 6), 112)
November 19, 2014 at 2:25 pm
I agree with Sean, you should store your dates as a date/time data type. You can easily convert them with a simple formula.
SELECT CAST( STUFF( oldDate, 1, 1, 19 + LEFT(oldDate, 1)) AS date),
CAST( CAST( 19000000 + oldDate AS CHAR(8)) AS date)
FROM( VALUES('0980412'),('1100323'))x(oldDate)
EDIT: Added a second formula.
November 19, 2014 at 2:50 pm
Here is yet another way...
with LegacyData (LegacyDate) as
(
select '0980412' union all
select '1100323'
)
select *
, cast(case left(LegacyDate, 1) when 0 then '19' else '20' end + SUBSTRING(LegacyDate, 2, 2) + SUBSTRING(LegacyDate, 4, 2) + RIGHT(LegacyDate, 2) as DATE)
from LegacyData
The important thing here is that the date datatype does NOT have a format. The format happens when you want to display a date datatype.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2014 at 3:36 am
Surely you can simply
DECLARE @oldDate varchar(10) = '0980423'
SELECT
DATEFROMPARTS
(
convert(Int,LEFT(@oldDate,3))+1900 --Year
,Substring(@oldDate,4,2)--Month
,Right(@oldDate,2)--Day
)
The output is a DATE datatype, this obviously assumes the data is correctly formatted and clean.
As others have said if you are converting the field to a new DB you might as well start changing the datatypes as well where necessary.
EDIT : I didn't see Steves suggestion until just now.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 21, 2014 at 5:53 am
There's no shortage of ways to do the actual conversion, but I think the most important point to understand the data type to use. If you store your dates in a column of DATE or DATETIME, then format is eliminated completely. The data will be stored as that data type. Like Luis and Sean said, the formatting of the data is done when you read it. You can format it however you want to.
November 25, 2014 at 8:19 am
Hi..Thanks for the reply's
sorry for the confusion...let me be more clear..
The data is extracted to sqlserver from DB2 and date columns are in this format 'CYYMMDD' with decimal datatype.
Now i am trying to extract this data into flatfiles, here i need to convert the date into mm/dd/yyyy.
As i have many date columns and many tables i want to use a function which converts this.
I was trying to create a function for this conversion.
SELECT CONVERT(datetime, CASE WHEN LEFT('col1', 1) = '1' THEN '20' ELSE '19' END + RIGHT('col1', 6), 112) from table1
is working fine.
I want to put this in a function.
Thanks guyz....
November 25, 2014 at 8:33 am
sqlquery29 (11/25/2014)
Hi..Thanks for the reply'ssorry for the confusion...let me be more clear..
The data is extracted to sqlserver from DB2 and date columns are in this format 'CYYMMDD' with decimal datatype.
Now i am trying to extract this data into flatfiles, here i need to convert the date into mm/dd/yyyy.
As i have many date columns and many tables i want to use a function which converts this.
I was trying to create a function for this conversion.
SELECT CONVERT(datetime, CASE WHEN LEFT('col1', 1) = '1' THEN '20' ELSE '19' END + RIGHT('col1', 6), 112) from table1
is working fine.
I want to put this in a function.
Thanks guyz....
So just break out the logic and pass in a variable. You might also realize that what you posted has string literals for 'col1' instead of the column. Take a shot at doing this yourself, you will learn a LOT more than if we just hand it to you. Feel free to post back with what you have tried and we can help nudge you to a solution.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2014 at 12:21 am
Hi Sean,
I tried writing the function
CREATE FUNCTION dbo.Convert_Date (@date_column decimal(7,0))
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
i am getting error :
Incorrect syntax near '@date'.
And again this conversion is failing for some date columns, as there are some values '0' in those columns.
Error :Conversion failed when converting date and/or time from character string.
I wanted to make it generic, Please help with this code.
I am new to this coding.
Thanks..
November 26, 2014 at 1:43 am
sqlquery29 (11/26/2014)
Hi Sean,I tried writing the function
CREATE FUNCTION dbo.Convert_Date (@date_column decimal(7,0))
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
i am getting error :
Incorrect syntax near '@date'.
And again this conversion is failing for some date columns, as there are some values '0' in those columns.
Error :Conversion failed when converting date and/or time from character string.
I wanted to make it generic, Please help with this code.
I am new to this coding.
Thanks..
The Issue you have is that you are converting the Input date to a DECIMAL, I thought the original format was a Database VARCHAR/NVARCHAR, so the String manipulations will possibly fail, change the function so that the @date_column input is a VARCHAR/NVARCHAR (I cant remember the exact conversion from DB2 data types to SQL).
/*
AS DECIMAL
*/
DECLARE @date_column DECIMAL(7,0) = '0011201'
declare @date date
set @date = CONVERT(DATE,CASE WHEN LEFT(@date_column,1) = '1' THEN '20'
ELSE '19' END + RIGHT(@date_column,6), 112)
/*
AS VARCHAR
*/
DECLARE @date_column VARCHAR(10) = '0011201'
declare @date date
set @date = CONVERT(DATE,CASE WHEN LEFT(@date_column,1) = '1' THEN '20'
ELSE '19' END + RIGHT(@date_column,6), 112)
GO
/*
--EG
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
*/
Above this demonstrates what your function is trying to do, as a NUMERIC if fails with a conversion error, with a VARCHAR it works.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 26, 2014 at 7:49 am
sqlquery29 (11/26/2014)
Hi Sean,I tried writing the function
CREATE FUNCTION dbo.Convert_Date (@date_column decimal(7,0))
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
i am getting error :
Incorrect syntax near '@date'.
And again this conversion is failing for some date columns, as there are some values '0' in those columns.
Error :Conversion failed when converting date and/or time from character string.
I wanted to make it generic, Please help with this code.
I am new to this coding.
Thanks..
As Jason said you have a datatype issue here. You can't do string manipulation on a decimal. Also, I would recommend using an inline table valued function instead of a scalar function. More flexible and the performance is better too. 😛
create function ConvertLegacyIntToDate
(
@LegacyDate char(7)
)
returns table with schemabinding as
return
select cast(case left(@LegacyDate, 1) when 0 then '19' else '20' end
+ SUBSTRING(@LegacyDate, 2, 2)
+ SUBSTRING(@LegacyDate, 4, 2) + RIGHT(@LegacyDate, 2) as DATE) as NewDate
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 26, 2014 at 8:36 am
I would still use an arithmetic formula instead of string manipulation.
CREATE FUNCTION ConvertLegacyIntToDate
(
@LegacyDate int
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
SELECT CAST( CAST( 19000000 + oldDate AS CHAR(8)) AS date) as NewDate
Or I would just use the formula each time. As Sean mentioned, scalar functions are bad for performance and can be replaced by inline table valued functions, but it seems overkill this time, IMHO.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply