Home » SQL & PL/SQL » SQL & PL/SQL » Query (11g)
Query [message #648260] Fri, 19 February 2016 08:13 Go to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Dear All,

My query is that I want to display date with spaces

1 9 0 2 2 0 1 6

I tried

Select To_Char(Sysdate, 'dd mm yyyy') from dual

My Result is

19 02 2016

but i want result as above.



Thanks In Advance
Re: Query [message #648263 is a reply to message #648260] Fri, 19 February 2016 08:30 Go to previous messageGo to next message
glmjoy
Messages: 187
Registered: September 2011
Location: KR
Senior Member
Select Substr(To_Char(Sysdate, 'dd'),1,1)||' '|| Substr(To_Char(Sysdate, 'dd'),2,1) ||' '||
Substr(To_Char(Sysdate, 'mm'),1,1)||' '|| Substr(To_Char(Sysdate, 'mm'),2,1)||' '||
Substr(To_Char(Sysdate, 'yyyy'),1,1)||' '|| Substr(To_Char(Sysdate, 'yyyy'),2,1)||' '||
Substr(To_Char(Sysdate, 'yyyy'),3,1)||' '|| Substr(To_Char(Sysdate, 'yyyy'),4,1) from Dual
Re: Query [message #648264 is a reply to message #648263] Fri, 19 February 2016 08:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
nice homework assignment!
Re: Query [message #648305 is a reply to message #648263] Fri, 19 February 2016 19:54 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
select  regexp_replace(to_char(sysdate,'ddmmyyyy'),'^(.)(.)(.)(.)(.)(.)(.)(.)$','\1 \2 \3 \4 \5 \6 \7 \8') dt
  from  dual
/

DT
---------------
1 9 0 2 2 0 1 6

SQL>


SY.

[Updated on: Fri, 19 February 2016 19:56]

Report message to a moderator

Re: Query [message #648309 is a reply to message #648305] Sat, 20 February 2016 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or just:
SQL> select rtrim(regexp_replace(to_char(sysdate,'ddmmyyyy'),'(.)','\1 ')) dt from dual;
DT
----------------------------------------------------------------------------------------
2 0 0 2 2 0 1 6

but this will not help OP to his question when yours would. Smile

[Edit: missing word]

[Updated on: Sat, 20 February 2016 13:26]

Report message to a moderator

Re: Query [message #648316 is a reply to message #648309] Sat, 20 February 2016 06:32 Go to previous message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Michel Cadot wrote on Sat, 20 February 2016 02:25

Or just:


Yes, it is a bit less less writing but a bit more processing.

SY.

Previous Topic: Oracle Dates query
Next Topic: filtering in a table by ignoring the records those are unnecessary anymore
Goto Forum:
  


Current Time: Thu Mar 28 15:54:05 CDT 2024