Display only Hours:Minutes:Seconds

Database

Display only Hours:Minutes:Seconds

Hi there, I sure hope someone can help me out as it has been a long time since I have done something like this.

I have a row item in a SQL query that has:

Pat_enc.Checkin_time (This is a DateTime field )
Displays data as follows:
2010-09-18 10:15:00
2010-09-05 17:45:00

I need to know how to display only the hours:minutes:seconds from each row of data so it appears like:
10:15:00
17:45:00

I also need to be able to use this in a case statement such as the following:

CASE
when Pat_enc.Checkin_time between '18:30:00' and '19:29:00' then '6:30-7:29'
when Pat_enc.Checkin_time between '19:30:00' and '20:30:00' then '7:30-8:30'
END SHIFT_GROUP

Please help. As I have not done this in a LOOOOOOOG time, I will need very specific instructions to make it easier for me to follow.

Thank you in advance for your time and efforts.

Kathleen
1 REPLY
Highlighted
Enthusiast

Re: Display only Hours:Minutes:Seconds

Hi KMEscherich,
The sql is pretty simple for your problem. i created a bteq to test the sql i had created. Below is the bteq execution of your problem. Let me know if this is wht you were looking for....

BTEQ 12.00.00.01 Tue Oct 19 01:19:36 2010

*** Logon successfully completed.
*** Teradata Database Release is 12.00.00.10
*** Teradata Database Version is 12.00.00.10
*** Transaction Semantics are BTET.
*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
.set separator '|'
+---------+---------+---------+---------+---------+---------+---------+----

drop table sushil.ts ;

*** Table has been dropped.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

CREATE SET TABLE sushil.ts ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
ts TIMESTAMP(0))
PRIMARY INDEX ( ts );

*** Table has been created.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

database sushil;

*** New default database accepted.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

insert into ts
values('2006-11-23 15:30:23')
;insert into ts
values('2010-09-18 10:15:00')
;insert into ts
values('2010-09-05 17:45:00')
;insert into ts
values('2010-10-20 19:45:00')
;insert into ts
values('2010-10-19 20:29:00')
;insert into ts
values('2010-10-21 21:15:00');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

*** Insert completed. One row added.

*** Insert completed. One row added.

*** Insert completed. One row added.

*** Insert completed. One row added.

*** Insert completed. One row added.

+---------+---------+---------+---------+---------+---------+---------+----

select ts (time(0)) as x
, case
when x between '17:30:00' and '19:29:00' then '6:30-7:29'
when x between '19:30:00' and '20:30:00' then '7:30-8:30'
else ' ' end as expected_result
, ts
from sushil.ts;

*** Query completed. 6 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

x|expected_result| ts
-------- --------------- -------------------
15:30:23| |2006-11-23 15:30:23
19:45:00|7:30-8:30 |2010-10-20 19:45:00
10:15:00| |2010-09-18 10:15:00
20:29:00|7:30-8:30 |2010-10-19 20:29:00
17:45:00|6:30-7:29 |2010-09-05 17:45:00
21:15:00| |2010-10-21 21:15:00

+---------+---------+---------+---------+---------+---------+---------+----

.exit
*** You are now logged off from the DBC.
*** Exiting BTEQ...
*** RC (return code) = 0