OraFAQ Forum: SQL & PL/SQL » Date SQL Query Issue

Home » SQL & PL/SQL » SQL & PL/SQL » Date SQL Query Issue (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date SQL Query Issue [message #408974]

Thu, 18 June 2009 09:44

drkeith
Messages: 2
Registered: February 2008

Junior Member

Question about a SQL query I'm creating for my oracle database. I have a field, C.USERDEF2 which has a whole number in it. I want to take this field and add it to a date A.CREATDDT to get a new Created Date.

The problem is when I run it, I get the Year 1900 for all of these. Can anyone provide any assistance?

Query
select
A.PONUMBER as "Product",
A.CREATDDT as "Created Date",
B.PRMSHPDTE as "Promised Ship Date",
B.Released_Date as "Released Date",
C.VENDORID,
C.USERDEF2,
DATEPART(dayofyear,A.CREATDDT) "Created Date in Number",
DATEPART(dayofyear,A.CREATDDT) + (C.USERDEF2) as "NEWDAY",
convert(datetime, DATEPART(dayofyear,A.CREATDDT) + (C.USERDEF2), 101) as "FINAL"
from Table1 A, Table2 B, Table3 C
where A.PONUMBER = B.PONUMBER
AND A.VENDORID = B.VENDORID
AND B.VENDORID = C.VENDORID
AND A.VENDORID = C.VENDORID
AND C.VENDORID in ('VendorC10', 'Vendor3L', 'Vendor3R')
group by A.PONUMBER, A.CREATDDT, B.PRMSHPDTE, B.Released_Date, C.VENDORID, C.USERDEF2

Results:
Product Created Date Promised Ship Date
0000003307 2007-03-22 00:00:00.000 2007-03-22 00:00:00.000

Released Date VENDORID USERDEF2 Created Date in Number
2007-03-22 00:00:00.000 VendorC10 2 81

NEWDAY FINAL
83 1900-03-25 00:00:00.000

Report message to a moderator

Re: Date SQL Query Issue [message #408975 is a reply to message #408974]

Thu, 18 June 2009 09:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT   a.ponumber                                    AS "Product",
         a.creatddt                                    AS "Created Date",
         b.prmshpdte                                   AS "Promised Ship Date",
         b.released_date                               AS "Released Date",
         c.vendorid,
         c.userdef2,
         Datepart(dayofyear,a.creatddt)                "Created Date in Number",
         Datepart(dayofyear,a.creatddt) + (c.userdef2) AS "NEWDAY",
         Convert(datetime,Datepart(dayofyear,a.creatddt) + (c.userdef2),
                 101) AS "FINAL"
FROM     table1 a,
         table2 b,
         table3 c
WHERE    a.ponumber = b.ponumber
         AND a.vendorid = b.vendorid
         AND b.vendorid = c.vendorid
         AND a.vendorid = c.vendorid
         AND c.vendorid IN ('VendorC10','Vendor3L','Vendor3R')
GROUP BY a.ponumber,
         a.creatddt,
         b.prmshpdte,
         b.released_date,
         c.vendorid,
         c.userdef2

use TO_CHAR() function

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Thu, 18 June 2009 09:52]

Report message to a moderator

Re: Date SQL Query Issue [message #409006 is a reply to message #408974]

Thu, 18 June 2009 12:35

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

1. When you store DATEs in VARCHAR2 fields, you're asking for trouble.
2. Years have 4 digits, not 2.
3. Oracle handles DATEs just fine. You do not need a function called DATEPART to do anything for you.

Of course, all of this is speculation as I am making at least three assumption as you have assumed we all know everything about your table, data and functions.

Report message to a moderator

Previous Topic:	Unable to insert XML Data
Next Topic:	Send sql query output to file or send by mail

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 15:58:38 CST 2024