concatenating date and time fields

[jdgonzalez]

Hi all,

I have two fields that represent date and time but are both stored as varchar. What I'd like to do is to concatenate them into one field before I export the data. The data looks like this: where hst_dt is in mmddyyyy and hst_tm is in hhmmss.

hst_dt hst_tm
07222004 012004

I'm want the output to be as date datatype as one field. I can get the hst_dt to work by doing to_date(hst_dt, 'mmddyyyy'), but when I do to_date(hst_tm,'hhmmss') is get 'not a valid month'.

Any help you can provide would be greatly appreciated.

 

[SantaMufasa]

JD,

You were very close. Here is your solution.

Section 1 -- Sample data:

select * from gonzalez;

HST_DT     HST_TM
---------- ------
07222004   012004

Section 2 -- Here is your correct TO_DATE conversion:

select to_date(hst_dt||hst_tm,'mmddyyyyhhmiss') from gonzalez;

TO_DATE(H
---------
22-JUL-04

Section 3 -- To confirm time component and to have a little fun with Oracle in Spanish:
[code]
alter session set nls_language = spanish;
select to_char(to_date(hst_dt||hst_tm,'mmddyyyyhhmiss'),
'"Fecha y tiempo: "fmDay "la" DD "de" month, yyyy "a las" fmhh24:mi:ss.')
Fecha_Tiempo from gonzalez;

FECHA_TIEMPO
-----------------------------------------------------------
Fecha y tiempo: Jueves la 22 de julio, 2004 a las 01:20:04.

Let us know if this does what you want.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:44 (22Jul04) UTC (aka "GMT" and "Zulu"), 10:44 (22Jul04) Mountain Time)

 

[sem]

Your mistake is that Oracle uses mm for month and mi for minutes.

Regards, Dima

http://seminihin.narod.ru

 

[jdgonzalez]

Musafa and Sem,

Thanks for your help. I got it to work. I can already see that working with SQL Server and Oracle at the same time will be quite an adventure.