?
Solved

Convert 3 Decimal Fields into 1 Date Field

Posted on 2014-07-21
29
Medium Priority
?
189 Views
Last Modified: 2014-07-23
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
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
  • 13
  • 10
  • 3
  • +2
29 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40209385
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 40209469
You should be able to use something like
dateadd(dd, PIODD, dateadd(mm, PIODM, dateadd(yy, PIODY-1900, '1900-01-01')))

Open in new window

to get a datetime.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40209487
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).  

dateadd(DAY, PIODD - 1, dateadd(MONTH, PIODM, dateadd(YEAR, PIODY-1900, '19000101')))
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 70

Expert Comment

by:Qlemo
ID: 40209508
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 Comment

by:CityofDelrayBeach
ID: 40209629
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
 
LVL 30

Expert Comment

by:hnasr
ID: 40209672
Try:
SELECT DATEFROMPARTS(2014,10,29);

DATEFROMPARTS(intYear, intMonth, intDay)

Open in new window

0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40209673
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)

Open in new window

0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40209682
DateFromParts is available only starting with MSSQL 2012, and here we are using 2008r2.
0
 

Author Comment

by:CityofDelrayBeach
ID: 40209768
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40209787
It's not a decimal, it's a datetime, so it should cast to date just fine.  Specifically what error are you getting?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40209857
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
set @d2=dateadd(day,@x1,dateadd(month,@x2,dateadd(year,@x3,@d2)));

set @d2=dateadd(day,-datepart(day,@d1),@d2);
set @d2=dateadd(month,-datepart(month,@d1),@d2);
set @d2=dateadd(year,-datepart(year,@d1),@d2);
select @d1, @d2;
--Result
--(No column name)	(No column name)
--2014-07-21	2013-11-22

Open in new window

0
 

Author Comment

by:CityofDelrayBeach
ID: 40209859
Scott - the source data type is decimal.

My string reads as follows:

(cast dateadd(DAY, PIODD - 1, dateadd(MONTH, PIODM - 1, dateadd(YEAR, PIODY-1900, '19000101'))) as PO_Date)

The error is:
 Incorrect syntax near 'dateadd'.  And the word CAST is underlined as are the words DAY, MONTH, and YEAR.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40210123
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)

Open in new window

Nevertheless, the decimals are used inside of datetime expressions, the result is a datetime, and the cast then converts to date.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40210512
What about this?
--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)));

Open in new window

0
 

Author Comment

by:CityofDelrayBeach
ID: 40211466
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 Comment

by:CityofDelrayBeach
ID: 40212812
hnasr - your queries do not work. Please read my original post and what I'm trying to do.
Thanks.
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 40212981
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)

Open in new window

0
 

Author Comment

by:CityofDelrayBeach
ID: 40213984
OK, then I assume I would add "As PO_Date" to the end to name my new column.
0
 

Author Comment

by:CityofDelrayBeach
ID: 40213998
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 40214010
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 Comment

by:CityofDelrayBeach
ID: 40214054
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 Closing Comment

by:CityofDelrayBeach
ID: 40214060
Tremendous help!
0
 

Author Comment

by:CityofDelrayBeach
ID: 40214719
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 40214760
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

Open in new window

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
else dateadd(DAY, PIODD - 1, dateadd(MONTH, PIODM - 1, dateadd(YEAR, PIODY-1900, '19000101')))
end as Date) as PO_Date

Open in new window

0
 

Author Comment

by:CityofDelrayBeach
ID: 40214967
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
else(dateadd(DAY, PIPODD - 1, dateadd(MONTH, PIPODM - 1, dateadd(YEAR, PIPODY-1900, '19000101'))) end as Date) as PO_Date

Error: Incorrect syntax near the keyword 'end'
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 40215014
There should be no ( after else
0
 

Author Comment

by:CityofDelrayBeach
ID: 40215148
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
   else dateadd(DAY, PIPODD - 1, dateadd(MONTH, PIPODM - 1, dateadd(YEAR, PIPODY-1900, '19000101')))
   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 Comment

by:CityofDelrayBeach
ID: 40215281
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 40215547
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

765 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