Solved

Convert 3 Date Columns into 1 Date Column

Posted on 2014-07-18
12
243 Views
Last Modified: 2014-07-24
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)+'-'+CONVERT(nvarchar(50),MM)+'-'+CONVERT(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.
0
Comment
Question by:CityofDelrayBeach
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 40205575
DATEFROMPARTS ( year, month, day )
0
 
LVL 32

Expert Comment

by:awking00
ID: 40206382
datefromparts requires integer parameters so, since you don't know the data types from DB2, you may need to convert them to integer first.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40207006
datefromparts() is only available in SQL Server 2012 onward, convert() with a date style works in all versions.

Assuming all those fields are integers

CONVERT(date,CONVERT(varchar(4),YYYY)+CONVERT(varchar(2),MM)+CONVERT(varchar(2),DD) ,112)  as PO_Date

If all those fields are aleady strings:

CONVERT(date,YYYY+MM+DD ,112)  as PO_Date

for date styles numbers see:
http://www.experts-exchange.com/Database/MS-SQL-Server/A_12315-SQL-Server-Date-Styles-formats-using-CONVERT.html
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:CityofDelrayBeach
ID: 40208864
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."
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40208892
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 ?
0
 

Author Comment

by:CityofDelrayBeach
ID: 40209031
Here's my query:

CREATE VIEW POs_FY_2013_2014 as
SELECT [PIPONO] as PO_Num
,CONVERT(date,CONVERT(varchar(4),PIODY)+CONVERT(varchar(2),PIODM)+CONVERT(varchar(2),PIODD) ,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].[HTEDTA].[PI320AP]
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!
0
 

Author Comment

by:CityofDelrayBeach
ID: 40209114
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].[HTEDTA].[PI320AP]
  where PIODY=2013 AND PIODM>=10 or PIODY=2014
GO
0
 

Author Comment

by:CityofDelrayBeach
ID: 40209325
Should I repost this question with all the info I have now?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40210217
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)

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

Open in new window

Do columns yyyy mm dd look ok?

note it only takes one whacky value to break the conversion of those fields into a date
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40210448
"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.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40210483
I don't think we have reached the point of needing a new question.
0
 

Author Comment

by:CityofDelrayBeach
ID: 40217202
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.
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question