Solved

Convert AS400 fields to SQL Date

Posted on 2013-06-17
5
372 Views
Last Modified: 2013-06-18
I need to combined three fields in our AS400 database in SQL 2008 view to represent a date, i.e. Month, Day, Year

Currently I have used a combination of the following and am not able to display the date information properly:
CONVERT (DateTime, CONVERT(varchar, dbo.PR503AP.PRBDMM) + '/' + CONVERT (varchar, dbo.PR503AP.PRBDDD) + '/' + CONVERT (varchar, dbo.PR503AP.PRBDYY), 110) AS Birth_Date

Doing so, I render YYYY-MM-DD 00:00:00

My desired display should be MM/DD/YYYY or MM-DD-YYYY
0
Comment
Question by:edrz01
[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
  • 3
5 Comments
 
LVL 5

Expert Comment

by:DOSLover
ID: 39254797
SQL Server's CAST and CONVERT functions can help as follows.

select CONVERT(varchar(10),CAST(dbo.PR503AP.PRBDYY+ '-' + dbo.PR503AP.PRBDMM+ '-' + dbo.PR503AP.PRBDDD as datetime),101) from ...;

Open in new window

0
 

Author Comment

by:edrz01
ID: 39254813
Thank you DOSLover, I will try this tomorrow when I'm back at work.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39254922
You are almost there. I'm assuming those 3 fields are integers. Below I've used a cte and indentation for clarity, but I believe you really just needed a "wrapper"

convert(varchar, <<what you have here>>, format-mask)

;with cte as (
              select 1980 as PRBDYY, 1 as PRBDMM, 17 as PRBDDD
              )
select   
  -- using recommended, unambiguous, YYYY-MM-DD
  convert(varchar,
          CONVERT (DateTime, 
                      CONVERT(varchar, cte.PRBDYY) 
                    + '-' 
                    + CONVERT (varchar, cte.PRBDMM) 
                    + '-' 
                    + CONVERT (varchar, cte.PRBDDD)
                  )  -- moved format mask from here
        , 110) -- change format mask to suit here
  AS Birth_Date
from cte
;

Open in new window

try this out at: http://sqlfiddle.com/#!3/1fa93/7707
note it isn't essential that you use YYY-MM-DD sequence - but I do recommend it.
0
 

Author Comment

by:edrz01
ID: 39256641
PortletPaul, I took your coding and modified it:

CONVERT (varchar, CONVERT (DateTime, CONVERT (varchar, dbo.PR503AP.PRBDYY) + '-' + CONVERT (varchar, dbo.PR503AP.PRBDMM) + '-' + CONVERT (varchar, dbo.PR503AP.PRBDDD)), 110) As Birth_Date

This gave me the date sequence that I was looking for i.e. MM-DD-YYYY
------
DOSLover, I did try your code and it gave rendered SQL errors.
0
 

Author Closing Comment

by:edrz01
ID: 39256648
Thank you for your recommendation, it pointed me in the direction I needed.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

617 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