Home » SQL & PL/SQL » SQL & PL/SQL » concatinating date
concatinating date [message #339061] Wed, 06 August 2008 12:25 Go to next message
suora08
Messages: 3
Registered: August 2008
Junior Member
Hi friends,
I have 3 fields in one table
1st )datetime----It contains date 08/03/2008
2nd)float----It contains hourminute 930 for time 9:30
3rd)varchar2-----it contains AM or PM
I have to concatenate three into one field which is datetime
Waiting for your reply,
Thanks,
Re: concatinating date [message #339065 is a reply to message #339061] Wed, 06 August 2008 12:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

use TO_DATE() function

[Updated on: Wed, 06 August 2008 12:31] by Moderator

Report message to a moderator

Re: concatinating date [message #339067 is a reply to message #339061] Wed, 06 August 2008 12:31 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Give me your mail-address and I will mail you my hourly rate.

Kidding, but hey, you started it..
Here at OraFAQ, we don't believe in "here's my problem, now you provide me with a SQL". We really love to help people, but we want to help them learn.
In order to being able to help you, you must first show us what YOU tried. Show us a copy/paste from sqlplus with your attempt and tell us where you got stuck.
Then you will get help.

If you even bother to use [code]tags, you can be dead certain you will have every regular's attention.
Re: concatinating date [message #339071 is a reply to message #339061] Wed, 06 August 2008 12:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TO_TIMESTAMP

Regards
Michel
Re: concatinating date [message #339082 is a reply to message #339067] Wed, 06 August 2008 13:06 Go to previous messageGo to next message
suora08
Messages: 3
Registered: August 2008
Junior Member
Hi,
I am really sorry for that, am new to forums. Here is what I am trying to do. I am trying to convert all three fields to to_char then converting the whole fields to to_date
somthing like this
SELECT to_date(To_char(e.Fld1) || ':'
TO_char(e.fld2,'HH12:MI:SS') || ':'
to_char(e.fld3,'MM/DD/YYYY HH12:MI:SS:AM'))
FROM e;
because the fld1 is date,fld2 is float,fld3 is varchar2 and values will be something like this fld1-08/05/2008 fld2-930 fld3-AM

Thanks,

Re: concatinating date [message #339084 is a reply to message #339061] Wed, 06 August 2008 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> I am trying to convert all three fields to to_char then converting the whole fields to to_date
Yes, that is the correct approach.
keep in mind that dealing with single quote marks can be a challenge.
Re: concatinating date [message #339095 is a reply to message #339084] Wed, 06 August 2008 14:15 Go to previous messageGo to next message
suora08
Messages: 3
Registered: August 2008
Junior Member
Hi,
When I am running the above query it is giving the error
ORA-01858: a non-numeric character was found where a numeric was expected

can anyone help
Thanks,
Sudha
Re: concatinating date [message #339098 is a reply to message #339095] Wed, 06 August 2008 14:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please, read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Use SQL*Plus, always copy and paste what you did instead or in addition of describing it.

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: concatinating date [message #339105 is a reply to message #339095] Wed, 06 August 2008 14:47 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
suora08 wrote on Wed, 06 August 2008 21:15
Hi,
When I am running the above query it is giving the error
ORA-01858: a non-numeric character was found where a numeric was expected

can anyone help
Thanks,
Sudha

In your to_date, you missed a parenthesis. Now the complete format-mask is used for fld3.

By the way, don't to_char fld3, as it is already a string.
Previous Topic: Sql query needed
Next Topic: size of objects
Goto Forum:
  


Current Time: Fri Dec 09 21:27:20 CST 2016

Total time taken to generate the page: 0.19414 seconds