# Convert 3 Decimal Fields into 1 Date Field

I'm pulling data from a Linked Server (DB2) and creating views in SQL 2008R2.  The source database has 3 fields for date which are decimal.  Here's my simple query:

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
FROM [DB2SRV].[DELRAYPD].[HTEDTA].[PI320AP]
where PIODY=2013 AND PIODM>=10 or PIODY=2014
GO

I want to combine the [PIODY] [PIODM] [PIODD] decimal fields from the source database to a date field in my View.  http://technet.microsoft.com/en-us/library/ms187928(v=sql.105).aspx tells me it must be an implicit conversion from decimal to datetime, but I'm having difficulty with the syntax.

Any help is greatly appreciated
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Microsoft SQL Server Developer, Architect, and AuthorCommented:
Show us a data mockup of what these values look like in the PI320AP table, and we'll provide SQL to convert to date.
0
Batchelor, Developer and EE Topic AdvisorCommented:
You should be able to use something like
``````dateadd(dd, PIODD, dateadd(mm, PIODM, dateadd(yy, PIODY-1900, '1900-01-01')))
``````
to get a datetime.
0
Senior DBACommented:
No points for me please.  Very close, but I think you need to subtract one day in the final calc.  Btw, 'YYYYMMDD' is 100% safe, YYYY-MM-DD is not (although for -01-01 there is no possible misinterpretation of the data, it's still best to always code dates as YYYYMMDD only).

0
Batchelor, Developer and EE Topic AdvisorCommented:
Pah, one day off, that is nitpicking :D.
And you are correct, I tell my fellow workers to use 'yyyymmdd' all the time for reliability reasons.
0
Author Commented:
That worked but I have two issues;

1.  The date is incorrect (instead of pulling the actual date of 2013-10-18 it's pulling 2013-11-18) but I assume the -1 does that so should I remove that?
2.  I don't want the time appended to the date. I'd like to get rid of all those zeroes for the time.

Thanks so much!
0
Commented:
Try:
SELECT DATEFROMPARTS(2014,10,29);

``````DATEFROMPARTS(intYear, intMonth, intDay)
``````
0
Batchelor, Developer and EE Topic AdvisorCommented:
Yes, we have to subtract one from the month as we do from the day, and if you want to have a date-only value convert to DATE:
``````(cast dateadd(DAY, PIODD - 1, dateadd(MONTH, PIODM - 1, dateadd(YEAR, PIODY-1900, '19000101'))) as date)
``````
0
Batchelor, Developer and EE Topic AdvisorCommented:
DateFromParts is available only starting with MSSQL 2012, and here we are using 2008r2.
0
Author Commented:
Hi Olemo. The cast statement doesn't work. I don't think you can convert decimal to date. I believe you can only convert decimal to datetime. We can likely handle removing the timestamp from the field when we create the reports so this may not be that important if it's too complicated/not possible. I'm also curious about having to subtract one from day and month. Is there an article that explains why just for my reference?
0
Senior DBACommented:
It's not a decimal, it's a datetime, so it should cast to date just fine.  Specifically what error are you getting?
0
Commented:
Using SQL 2014:

Check this and see if you can make use of it.
It starts with the current date then uses dataadd and datepart functions to get the required date from the 3 integer values.
``````declare @d1 date, @d2 date
set @d1=getdate();

select @d1;

--2013-11-22
declare @x1 integer=22
declare @x2 integer=11
declare @x3 integer=2013

set @d2=@d1

select @d1, @d2;
--Result
--(No column name)	(No column name)
--2014-07-21	2013-11-22
``````
0
Author Commented:
Scott - the source data type is decimal.

The error is:
Incorrect syntax near 'dateadd'.  And the word CAST is underlined as are the words DAY, MONTH, and YEAR.
0
Batchelor, Developer and EE Topic AdvisorCommented:
Sorry for that. Indeed it has to be
``````cast (dateadd(DAY, PIODD - 1, dateadd(MONTH, PIODM - 1, dateadd(YEAR, PIODY-1900, '19000101'))) as PO_Date)
``````
Nevertheless, the decimals are used inside of datetime expressions, the result is a datetime, and the cast then converts to date.
0
Commented:
--Replace @y, @m, @d with your respective year, month, and day fields:

``````select convert (date,cast(@y as nvarchar(4)) + '-' + cast(@m as nvarchar(2)) + '-' + cast(@d as nvarchar(2)));
``````
0
Author Commented:
Olemo - when I run that query I get this error message:

Type PO_Date is not a defined system type.

I see an answer in this post using the FLOOR command  http://www.experts-exchange.com/Database/Miscellaneous/Q_25358191.html but I can't figure out the syntax for my query!
0
Author Commented:
hnasr - your queries do not work. Please read my original post and what I'm trying to do.
Thanks.
0
Batchelor, Developer and EE Topic AdvisorCommented:
Ooops, where is that PO_Date coming from???! No clue how that got into my post. Correct expression:
``````cast (dateadd(DAY, PIODD - 1, dateadd(MONTH, PIODM - 1, dateadd(YEAR, PIODY-1900, '19000101'))) as Date)
``````
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
OK, then I assume I would add "As PO_Date" to the end to name my new column.
0
Author Commented:
OK, so that works but on one of my Views I get the following error when I "Select Top (200) rows:

"Adding a value to a 'datetime' column caused an overflow."

Ugh!
0
Batchelor, Developer and EE Topic AdvisorCommented:
Then one of the values must be off in a horrible way. Like adding a month of 2 millions. Even 100 months would be fine, and similar for the day. I would check the year first for being within the usual date boundaries, then month being 1-12 and day 1-31.
0
Author Commented:
There are a lot of zero (0) values.  Would that cause it?  I don't see any other numbers in the source tables date fields that are out of range.
0
Author Commented:
Tremendous help!
0
Author Commented:
Qlemo - can you help with the overflow error?

Maybe it will be easier if I just pull the 3 fields over as a char field and not convert it to a "date" field?  I don't see anything in the source data that would cause the error other than a lot of zeroes.

Thanks.
0
Batchelor, Developer and EE Topic AdvisorCommented:
Thought you already answered your own question regarding the zeros. Of course that can cause an overflow (or rather an underflow if all date parts are zero).

In your original question you restricted year and month already to something recent, (Oct 2013 and later).
To make sure the values are in a valid range (i.e. non-zero), just add a condition to the WHERE clause:
``````and PIODY > 0 and PIODM > 0 and PIODD > 0
``````
That will, of course, reduce the rows to get retrieved. If you need all rows, even if having zero dates, then you'll need something more complex in your view, and a date you can use as replacement (like the NULL date):
``````cast (
case when day = 0 or month = 0 or year = 0 then NULL
end as Date) as PO_Date
``````
0
Author Commented:
Sorry about that. I am writing multiple scripts to convert the date and what I posted initially was just an example. Yes, I need all rows, even those with zeroes to be converted to dates.

My query now:
cast(case when day=0 or month=0 or year=0 then NULL

Error: Incorrect syntax near the keyword 'end'
0
Batchelor, Developer and EE Topic AdvisorCommented:
There should be no ( after else
0
Author Commented:
I'm such a moax!  Thanks Qlemo! I had to change my final string to:

,cast(case when PIPODD = 0 or PIPODM = 0 or PIPODY = 0 then NULL
end as Date) as PO_Date

and WALA!

Wish I could hug you!  Not sure if you will avoid me (lol) but I will likely be posting more questions while working on this project!  Look for me!  ;)
0
Author Commented:
One more thing Qlemo...

wouldn't the 1st case statement be better with AND as opposed to OR as follows:

,cast(case when PIPODD=0 and PIPODM=0 or PIPODY=0 then NULL

That way I only get NULL if all 3 fields are zero.
0
Batchelor, Developer and EE Topic AdvisorCommented:
My assumption is that no zero is allowed at all, and any zero renders the date invalid, so it is better to substitue with NULL if in doubt.
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.