November 21st, 2010, 09:23 PM
|
|
Registered User
|
|
Join Date: Nov 2010
Posts: 2
Time spent in forums: 46 m 59 sec
Reputation Power: 0
|
|
|
I worked it out, this is how:
Code:
SELECT
date,
CASE
WHEN cast(left(cast(end_date AS TIME),2) AS INTEGER)>12
THEN cast(cast(left(cast(end_date AS TIME),2) AS INTEGER)-12 AS VARCHAR(2)) || cast(right(left(cast(end_date AS TIME),5),3) AS VARCHAR(3))||'PM'
ELSE cast(cast(left(cast(end_date AS TIME),2) AS INTEGER) AS VARCHAR(2)) || cast(right(left(cast(end_date AS TIME),5),3) AS VARCHAR(3))||'AM'
END as "Time",
event
FROM EVENTS
This pulls the relevant characters out of the date, convert them to integers so it can do the calculations, then converts it to varchar so it can concatenate the text. Yes it is long-winded but it works.
Enjoy
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
