Home » SQL & PL/SQL » SQL & PL/SQL » Date SQL Query Issue (Oracle 10g)
Date SQL Query Issue [message #408974] Thu, 18 June 2009 09:44 Go to next message
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
Re: Date SQL Query Issue [message #408975 is a reply to message #408974] Thu, 18 June 2009 09:49 Go to previous messageGo to next message
BlackSwan
Messages: 25036
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 Go to previous message
joy_division
Messages: 4640
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.
Previous Topic: Unable to insert XML Data
Next Topic: Send sql query output to file or send by mail
Goto Forum:
  


Current Time: Mon Dec 05 03:04:45 CST 2016

Total time taken to generate the page: 0.07417 seconds