Perl is dropping time from a date retrieved in a query

[RottPaws]

I've got a script that runs a query against an Oracle database and then generates a report with that information.

When I run the query, I get a date/time like 11/4/2003 2:16:52 PM in the results for the COMMENT_DT field.

I loop through the recordset with:
while (($mso,$ord_type,$action,$ccdd,$user,$comment,$cmt_dt,$rep,$req_type) = $orasth->fetchrow) {

But when I print the value of $cmt_dt all I get is 04-NOV-03

What do I have to do to pull the time with the date?

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[siberian]

Oracle gives out default date formats unless you tell it to do otherwise.

You have to set the date format variable in oracle before you retrieve the date. I forget the exact syntax but it is something in the SET SESSION realm.

Someone else will probably give you the exact syntax.

 

[RottPaws]

What format does it need to be in?

Like I said, when I run the query external to Perl, it includes the time. But when I put the date into a variable, the time gets dropped.

If it needs to be in a specific format, I can convert it in the query. I just need to know the format to use.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[siberian]

No you are misunderstanding me. This is not a PERL or DATA issue, its the way Oracle parses dates. The real data is in the database, you just have to properly tell oracle how to get it out.
Here I went and did some research for you.

SELECT BLA,BLA,TO_CHAR(sysdate, 'MM DD YYYY HH24:MI:SS') AS time, BLA ,BLA FROM BLA

Replace sysdate with your field name that contains the date.

 

[RottPaws]

I understood exactly what you were saying. And you answered my question perfectly.

Thank you!

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!

 

[usige]

Rott,
You can also just modify your NLS_DATE_FORMAT for the current session. I do something like the following in my scripts so that I don't have to do to_char's on all my selects:

$dbh->do( q{ alter session set nls_date_format = 'YYYY.MM.DD HH24:MI:SS' } );

 

[RottPaws]

Excellent! Thank you for the tip.

_________
Rott Paws

...It's not a bug. It's an undocumented feature!!!