CityofDelrayBeach
asked on
Convert 3 Date Columns into 1 Date Column
SQL 2008R2:
I'm pulling data from a Linked Server (DB2) and creating views. The tables in the link servers have dates in 3 fields - let's just call the 3 fields (YYYY) (MM) (DD). I don't know the data type of these fields on the DB2 side.
When I use the + in my query it adds up the 3 columns and gives me a total. The only way I have been able to accomplish getting these 3 fields into 1 field is using the following:
CONVERT(nvarchar(50),YYYY) +'-'+CONVE RT(nvarcha r(50),MM)+ '-'+CONVER T(nvarchar (50),DD) as PO_Date
This works but I want the new "PO_Date" field to be a date field.
Any help is greatly appreciated.
I'm pulling data from a Linked Server (DB2) and creating views. The tables in the link servers have dates in 3 fields - let's just call the 3 fields (YYYY) (MM) (DD). I don't know the data type of these fields on the DB2 side.
When I use the + in my query it adds up the 3 columns and gives me a total. The only way I have been able to accomplish getting these 3 fields into 1 field is using the following:
CONVERT(nvarchar(50),YYYY)
This works but I want the new "PO_Date" field to be a date field.
Any help is greatly appreciated.
DATEFROMPARTS ( year, month, day )
datefromparts requires integer parameters so, since you don't know the data types from DB2, you may need to convert them to integer first.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi everyone, and thanks for the responses.
PortletPaul: Your first statement worked, but when I try to run the "View" I get the following error:
"Error Message: Conversion failed when converting date and/or time from character string."
PortletPaul: Your first statement worked, but when I try to run the "View" I get the following error:
"Error Message: Conversion failed when converting date and/or time from character string."
when you run ... what? have you created a view, and it is failing?
Perhaps you could post the sql you are using?
Have you determined what the data types are of those 3 fields from DB2?
are they ALWAYS suitable as YYYY MM DD ?
Perhaps you could post the sql you are using?
Have you determined what the data types are of those 3 fields from DB2?
are they ALWAYS suitable as YYYY MM DD ?
ASKER
Here's my query:
CREATE VIEW POs_FY_2013_2014 as
SELECT [PIPONO] as PO_Num
,CONVERT(date,CONVERT(varc har(4),PIO DY)+CONVER T(varchar( 2),PIODM)+ CONVERT(va rchar(2),P IODD) ,110) as PO_Date
,[PIOVN] as Vend_Num
,[PIREQR] as Requestor
,[PIOEA] as Acct_No
,[PIOUC] as Amount1
,[PIFOUC] as Amount2
,[PIOQ] as Amount3
FROM [DB2SRV].[DELRAYPD].[HTEDT A].[PI320A P]
where PIODY=2013 AND PIODM>=10 or PIODY=2014
GO
It creates the view fine (no errors). But when I try to run the view (return top 200 rows) I get the error I posted.
I am trying to find out the data types of the 3 fields in DB2. I've left a message for my contact. As soon as I know I will post.
Thanks so much Paul!
CREATE VIEW POs_FY_2013_2014 as
SELECT [PIPONO] as PO_Num
,CONVERT(date,CONVERT(varc
,[PIOVN] as Vend_Num
,[PIREQR] as Requestor
,[PIOEA] as Acct_No
,[PIOUC] as Amount1
,[PIFOUC] as Amount2
,[PIOQ] as Amount3
FROM [DB2SRV].[DELRAYPD].[HTEDT
where PIODY=2013 AND PIODM>=10 or PIODY=2014
GO
It creates the view fine (no errors). But when I try to run the view (return top 200 rows) I get the error I posted.
I am trying to find out the data types of the 3 fields in DB2. I've left a message for my contact. As soon as I know I will post.
Thanks so much Paul!
ASKER
Paul - the source columns are decimal. I tried changing the query from varchar to decimal and it says "Explicit conversion from data type decimal to date is not allowed." This http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx says I need an implicit statement using the datetime function. I'm searching but if you can help I greatly appreciate it.
CREATE VIEW POs_FY_2013_2014 as
SELECT [PIPONO] as PO_Num
,[PIODY] as PO_Year
,[PIODM] as PO_Month
,[PIODD] as PO_Day
,[PIOVN] as Vend_Num
,[PIREQR] as Requestor
,[PIOEA] as Acct_No
,[PIOUC] as Amount1
,[PIFOUC] as Amount2
,[PIOQ] as Amount3
FROM [DB2SRV].[DELRAYPD].[HTEDT A].[PI320A P]
where PIODY=2013 AND PIODM>=10 or PIODY=2014
GO
CREATE VIEW POs_FY_2013_2014 as
SELECT [PIPONO] as PO_Num
,[PIODY] as PO_Year
,[PIODM] as PO_Month
,[PIODD] as PO_Day
,[PIOVN] as Vend_Num
,[PIREQR] as Requestor
,[PIOEA] as Acct_No
,[PIOUC] as Amount1
,[PIFOUC] as Amount2
,[PIOQ] as Amount3
FROM [DB2SRV].[DELRAYPD].[HTEDT
where PIODY=2013 AND PIODM>=10 or PIODY=2014
GO
ASKER
Should I repost this question with all the info I have now?
Creating the view (without errors) simply means the syntax is ok.
When applying the syntax to the data some problem is discovered. Now we know the source data is decimal that error might simply be "data truncation" but I'm guessing. Try the following and inspect the results.
All 3 have to actually conform to logical rules for conversion to a date. e.g.
we would expect year to be YYYY (only, no decimal part) not 5 digits, not 3
month can only be in range 1 to 12 (not zero to 11)
day can only be in range 1 to 31, but (for example, 31 with month 6 is wrong)
note it only takes one whacky value to break the conversion of those fields into a date
When applying the syntax to the data some problem is discovered. Now we know the source data is decimal that error might simply be "data truncation" but I'm guessing. Try the following and inspect the results.
All 3 have to actually conform to logical rules for conversion to a date. e.g.
we would expect year to be YYYY (only, no decimal part) not 5 digits, not 3
month can only be in range 1 to 12 (not zero to 11)
day can only be in range 1 to 31, but (for example, 31 with month 6 is wrong)
select
convert(varchar(50),PIODY)
, convert(varchar(50),PIODM)
, convert(varchar(50),PIODD)
, convert(varchar(4),convert(int,PIODY)) as yyyy
, convert(varchar(2),convert(int,PIODM)) as mm
, convert(varchar(2),convert(int,PIODD)) as dd
, PIODY
, PIODM
, PIODD
from adb2source
Do columns yyyy mm dd look ok?note it only takes one whacky value to break the conversion of those fields into a date
"Should I repost this question with all the info I have now? "
If no comment is a suitable answer, then yes. You may add a new comment explaining the issue but don't deviate from the original question. If you intend any modification, then accept the best comment in this thread and post a new question.
If no comment is a suitable answer, then yes. You may add a new comment explaining the issue but don't deviate from the original question. If you intend any modification, then accept the best comment in this thread and post a new question.
I don't think we have reached the point of needing a new question.
ASKER
I posted another question (prior to your response) explaining my need more clearly. I finally got the solution as follows:
cast (case when day = 0 or month = 0 or year = 0 then NULL
else dateadd(DAY, [FIELD] - 1, dateadd(MONTH, [FIELD] - 1, dateadd(YEAR, [FIELD]-1900, '19000101')))
end as Date) as PO_Date
Thanks for your help.
cast (case when day = 0 or month = 0 or year = 0 then NULL
else dateadd(DAY, [FIELD] - 1, dateadd(MONTH, [FIELD] - 1, dateadd(YEAR, [FIELD]-1900, '19000101')))
end as Date) as PO_Date
Thanks for your help.