edrz01
asked on
Convert AS400 fields to SQL Date
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
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
ASKER
Thank you DOSLover, I will try this tomorrow when I'm back at work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
Thank you for your recommendation, it pointed me in the direction I needed.
Open in new window