OraFAQ Forum: SQL & PL/SQL » Sql query showing time problem

Home » SQL & PL/SQL » SQL & PL/SQL » Sql query showing time problem (Oracle,11g,Windows Xp)

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

Sql query showing time problem [message #604881]

Sun, 05 January 2014 06:59

myadi26
Messages: 7
Registered: December 2012
Location: india

Junior Member

Hi everybody,

Here, I am trying to write a query which will show in-time
and out-time of employees.

I am using this table:

SQL> desc emp_test;
 Name                               Null?    Type
 ------------------          -------------- -------- 
 ID                                          NUMBER
 IN_TIME                               VARCHAR2(100)          
 OUT_TIME                              VARCHAR2(100)

Data in my table:

SQL> select in_time, out_time
  2  from emp_test;

IN_TIME                OUT_TIME
---------------------- ----------------------
08:00                  08:00
10:00                  08:00
09:00                  09:00
12:00                  08:00

From the front-end application user may enter in-time value
08:00(actually its 8:00 am) and out-time is 08:00(which is
8 pm).I have to show output like:

IN_TIME                OUT_TIME
---------------  ----------------------
08:00 am                08:00 pm
10:00 am                08:00 pm
09:00 am                09:00 pm
12:00 pm                08:00 pm

My query is like:

SQL> ed
Wrote file afiedt.buf

  1  SELECT TO_CHAR(TO_DATE(IN_TIME,'HH24:MI'),'HH24:MI am') "in",
  2  TO_CHAR(TO_DATE(out_TIME,'HH24:MI'),'HH24:MI pm') "out"
  3* FROM EMP_TEST
SQL> /

in       out
-------- --------
08:00 am 08:00 am  --it should be 8 pm
10:00 am 08:00 am
09:00 am 09:00 am  --9 pm
12:00 pm 08:00 am

please help

Report message to a moderator

Re: Sql query showing time problem [message #604882 is a reply to message #604881]

Sun, 05 January 2014 07:07

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

Senior Member

Do not use a character type and do not store in that way. Take the input as a DATE type with complete timestamp. Then use the proper format mask to retrieve the time part.

Look at this asktom link

[Updated on: Sun, 09 March 2014 03:02] by Moderator

Report message to a moderator

Re: Sql query showing time problem [message #604885 is a reply to message #604882]

Sun, 05 January 2014 08:42

myadi26
Messages: 7
Registered: December 2012
Location: india

Junior Member

Thanks for your reply.I can understand from the link that storing
date & time as varchar2 datatype is not recommended.But I dont
have such privileges so that I can alter datatype of in-time
& out-time column.Can I change my query to meet the requirement?

Regards
Sudeshna

Report message to a moderator

Re: Sql query showing time problem [message #604886 is a reply to message #604885]

Sun, 05 January 2014 09:27

flyboy
Messages: 1903
Registered: November 2006

Senior Member

I see no need of VARCHAR2-DATE-VARCHAR2 conversion. If you "know" that OUT_TIME is always 'pm' (and IN_DATE always 'am' except 12:whatever), why not simply concatenate it to the column content?

select in_time||case when substr(in_time, 1,2) = '12' then ' pm' else ' am' end in_time,
       out_time||' pm' out_time
from emp_test;

You may change the CASE expression for any logic determining the am/pm/fm/whatever value. And do not be surprised when it contains any garbage - it is the benefit of VARCHAR2 data type.

It would be nice if the "front-end application" would be fixed to store data in format you want to report.
Or, much better, store dates as DATE data type (with proper change of its treating).
(and yes, whoever designed that cr*p should be fired for incompetency)

Report message to a moderator

Previous Topic:	How to send a Blob variable to a remote server
Next Topic:	Help In optimizing My Query Required Urgently

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 14:52:39 CDT 2024