OraFAQ Forum: SQL & PL/SQL » date format from string

Home » SQL & PL/SQL » SQL & PL/SQL » date format from string (Oracle 11g)

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

date format from string [message #638714]

Fri, 19 June 2015 02:19

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

Hello

I would like to convert value of string to default oracle date format.

String values looks like:

select '[2015-06-18 14:00:31,204]' string_value from  dual

I would like to get date format: 18.06.2015 14:00:31

Does anybody let me know how to do that?

Thanks

Martin

[Updated on: Fri, 19 June 2015 02:21]

Report message to a moderator

Re: date format from string [message #638715 is a reply to message #638714]

Fri, 19 June 2015 02:32

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

If you're storing it in the database, substring it out and use to_date.

Report message to a moderator

Re: date format from string [message #638718 is a reply to message #638714]

Fri, 19 June 2015 02:34

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

Senior Member
Account Moderator

What about SUBSTR that start at index 2 and ends at character, or associated index, your string is fixed format so it is easy just to count the characters, isn't it?

Report message to a moderator

Re: date format from string [message #638721 is a reply to message #638718]

Fri, 19 June 2015 03:23

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

This string is stored in the table as varchar. I need to make it like datatype DATE.

Report message to a moderator

Re: date format from string [message #638724 is a reply to message #638714]

Fri, 19 June 2015 03:30

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mape wrote on Fri, 19 June 2015 12:49

I would like to convert value of string to default oracle date format.

There is no default format for date. The format model is for display purpose. Oracle doesn't store in the format you see. Date is stored in 7 bytes in an internal Oracle's proprietary format.

Assuming that the string is stored in a fixed format:

SQL> WITH DATA AS(
  2  SELECT '[2015-06-18 14:00:31,204]' string_value FROM  dual
  3  )
  4  SELECT to_char(
  5                 to_date(
  6                         SUBSTR(string_value, 2, 19),
  7                         'YYYY-MM-DD HH24:MI:SS'
  8                        ),
  9                 'DD.MM.YYYY HH24:MI:SS'
 10                ) my_format
 11  FROM DATA;

MY_FORMAT
-------------------
18.06.2015 14:00:31

SQL>

TO_CHAR is used to display the date value in your desired format. For date arithmetic, use TO_DATE.

[Updated on: Fri, 19 June 2015 03:32]

Report message to a moderator

Re: date format from string [message #638725 is a reply to message #638724]

Fri, 19 June 2015 03:34

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

Excellent solution, it works perfectly.

Thanks a lot

Report message to a moderator

Re: date format from string [message #638726 is a reply to message #638721]

Fri, 19 June 2015 03:35

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

mape wrote on Fri, 19 June 2015 13:53

This string is stored in the table as varchar. I need to make it like datatype DATE.

I think you need to fix your design. Store date as date datatype, and the delimited value in different column as number. Storing the two different values in a single column is not a good idea. Specially when you are dealing with datetime values you need to be more careful.

Report message to a moderator

Re: date format from string [message #638727 is a reply to message #638724]

Fri, 19 June 2015 03:36

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Quote:

There is no default format for date.

Not exactly true Lalit. I know what you're meaning to say, but that sentence is misleading. Granted the rest of your post seems to clarify this, but that first statement is the 'anchor' that some may get hooked on.

Report message to a moderator

Re: date format from string [message #638728 is a reply to message #638727]

Fri, 19 June 2015 03:39

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Hi Jim,

That reminds me of this https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/ Should have posted it instead.

Report message to a moderator

Re: date format from string [message #638730 is a reply to message #638728]

Fri, 19 June 2015 03:42

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Aye, that's a great wee article.

Report message to a moderator

Re: date format from string [message #638808 is a reply to message #638730]

Mon, 22 June 2015 03:21

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

I would like to get fixed format (see below )into date format

to_char(
                   to_date(
                           SUBSTR(col1, 2, 19),
                           'YYYY-MM-DD HH24:MI:SS'
                          ),
                   'DD.MM.YYYY HH24:MI:SS'
                 )

How should I do that?
Thanks

Report message to a moderator

Re: date format from string [message #638809 is a reply to message #638808]

Mon, 22 June 2015 03:25

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

Senior Member
Account Moderator

Quote:

I would like to get fixed format (see below )into date format

What does this mean?
To state in other words what has already been said.
If your target is of DATE datatype, format is irrelevant, a DATE has no format.
If your target is a string then use TO_CHAR as you did.

Report message to a moderator

Re: date format from string [message #638810 is a reply to message #638809]

Mon, 22 June 2015 03:26

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

I mean to convert string into DATE datatype

Report message to a moderator

Re: date format from string [message #638811 is a reply to message #638810]

Mon, 22 June 2015 03:28

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

Senior Member
Account Moderator

So use TO_DATE on SUBSTR as it has been posted above.

Report message to a moderator

Re: date format from string [message #638812 is a reply to message #638811]

Mon, 22 June 2015 03:31

mape
Messages: 298
Registered: July 2006
Location: Slovakia

Senior Member

When I use sql statement like this:

 to_date( SUBSTR(col1, 2, 19),
                           'YYYY-MM-DD HH24:MI:SS'
                          )

I got records for instance like 21.06.2015 005828, but I need to looks like 21.06.2015 00:58:28

[Updated on: Mon, 22 June 2015 03:32]

Report message to a moderator

Re: date format from string [message #638813 is a reply to message #638812]

Mon, 22 June 2015 03:37

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

Senior Member
Account Moderator

Quote:

When I use sql statement like this:
...
I got records for instance like

No, you have not that, you have something you cannot see, what you see is the conversion of the date into a string and if you want a specific format for this string then you have to use TO_CHAR on the DATE.

Report message to a moderator

Re: date format from string [message #638814 is a reply to message #638813]

Mon, 22 June 2015 04:22

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Read the link Lalit posted above, it explains how dates actually work, cause they don't work the way you think they do.

Report message to a moderator

Previous Topic:	require output in described order
Next Topic:	SQL Query Case and Count

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 07:40:49 CDT 2024