Solved

Convert 3 Decimal Fields into 1 Date Field

Posted on 2014-07-21
29
172 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
  • 13
  • 10
  • 3
  • +2
29 Comments
 
LVL 65

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 68

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:ScottPletcher
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
 
LVL 68

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 68

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 68

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:ScottPletcher
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 68

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 68

Accepted Solution

by:
Qlemo earned 500 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 68

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 68

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 68

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 68

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

706 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now