OraFAQ Forum: SQL & PL/SQL » Can someone help me with this

Home » SQL & PL/SQL » SQL & PL/SQL » Can someone help me with this

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

Can someone help me with this [message #605477]

Thu, 09 January 2014 13:40

pingiliarjun
Messages: 2
Registered: January 2014
Location: Software Engineer

Junior Member

When I run the below Query

UPDATE INT.TABLE_NAME
SET CREATE_DTTM = TO_DATE(TO_DATE(TO_CHAR('20130111'),'YYYYMMDD'),'DD-MON-YYYY HH24:MI:SS')
WHERE FILL_DT <='20131231'

I am getting output as

11-JAN-0013 00:00:00 but the output should be 11-JAN-2013 00:00:00

If you see the year it is 0013 which is incorrect. Can someone help me with this.

Thanks

Report message to a moderator

Re: Can someone help me with this [message #605478 is a reply to message #605477]

Thu, 09 January 2014 13:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

what datatype is CREATE_DTTM ?

>I am getting output as
>11-JAN-0013 00:00:00 but the output should be 11-JAN-2013 00:00:00

what is complete SQL query that produces results above?

Report message to a moderator

Re: Can someone help me with this [message #605479 is a reply to message #605477]

Thu, 09 January 2014 13:50

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Please read this excellent blog from Ed Stevens, it will show you how you should be dealing with dates.

Report message to a moderator

Re: Can someone help me with this [message #605480 is a reply to message #605478]

Thu, 09 January 2014 13:51

pingiliarjun
Messages: 2
Registered: January 2014
Location: Software Engineer

Junior Member

The CREATE_DTTM datatype is date.

The complete update query is

UPDATE RX_CLAIM_PRE_LOAD
SET CREATE_DTTM = TO_DATE(TO_DATE(TO_CHAR(RX_FILL_DT_SK),'YYYYMMDD'),'DD-MON-YYYY HH24:MI:SS')
WHERE RX_FILL_DT_SK <='20131231'
AND CREATE_DTTM > TO_DATE('01/03/2014 00:00:00','MM/DD/YYYY HH24:MI:SS');

The RX_FILL_DT_SK is number. So I am converting a number to character and then date.

[Updated on: Thu, 09 January 2014 13:52]

Report message to a moderator

Re: Can someone help me with this [message #605481 is a reply to message #605480]

Thu, 09 January 2014 13:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>The RX_FILL_DT_SK is number.
so why do you compare it to a string as below?
>WHERE RX_FILL_DT_SK <='20131231'

>TO_DATE(TO_DATE(TO_CHAR(RX_FILL_DT_SK),'YYYYMMDD'),'DD-MON-YYYY HH24:MI:SS')
why TO_DATE(TO_DATE...?
You should NEVER use TO_DATE on any DATE datatype!

Report message to a moderator

Re: Can someone help me with this [message #605482 is a reply to message #605480]

Thu, 09 January 2014 14:49

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SET CREATE_DTTM = TO_DATE('20130111','YYYYMMDD')

Report message to a moderator

Re: Can someone help me with this [message #605716 is a reply to message #605482]

Mon, 13 January 2014 15:56

Bill B
Messages: 1971
Registered: December 2004

Senior Member

UPDATE RX_CLAIM_PRE_LOAD
SET CREATE_DTTM = trunc(RX_FILL_DT_SK)
WHERE RX_FILL_DT_SK <= to_date('20131231','YYYYMMDD')
AND CREATE_DTTM > TO_DATE('01/03/2014','MM/DD/YYYY');

Report message to a moderator

Previous Topic:	Re: XML issue (split from hijacked thread http://www.orafaq.com/forum/t/189540/)
Next Topic:	Syntax for similar Pattern between the tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 20:34:21 CDT 2024