Roy

> IF(
> EXTRACT(HOUR FROM _Timestamp) < 10
> THEN '0'+CAST(EXTRACT(HOUR FROM _Timestamp) AS VARCHAR(1))+':00 AM'
> ELSE IF(EXTRACT(HOUR FROM _Timestamp) <=12
> THEN CAST(EXTRACT(HOUR FROM _Timestamp) AS VARCHAR(2))+':00 AM'
> ELSE IF(EXTRACT(HOUR FROM _Timestamp)-12 < 10
> THEN '0'+CAST(EXTRACT(HOUR FROM _Timestamp)-12 AS VARCHAR(1))+':00 PM'
> ELSE CAST(EXTRACT(HOUR FROM _Timestamp)-12 AS VARCHAR(2))+':00 PM')))
> AS RightFormat

It would have been easier to quote the OP <vbg>

..... was hoping to find was a way to format the hour into something
similar to "08:00" or "14:00".

John

errrrr

must get some new  glasses

John

Roy Lambert wrote:

Dar


OK so you've pretty much got it from John's post. But here's the other bit you asked for

CAST(CAST(EXTRACT(HOUR FROM TakenOn) AS VARCHAR(2))+':00' AS TIME) AS SnapHour


Roy Lambert [Team Elevate]



====== Roy,
Thanks a million for this, I think this might be what I need alright - thanks for your
effort. Superb...
Cheers..
Dar...

Roy Lambert wrote:

Uli

>which doesn't consider the format he wants: e.g. 02:00 p.m.

errr

<<format the hour into something
similar to "08:00" or "14:00".>>

But just to show willing

IF(
EXTRACT(HOUR FROM _Timestamp) < 10
THEN '0'+CAST(EXTRACT(HOUR FROM _Timestamp) AS VARCHAR(1))+':00 AM'
ELSE IF(EXTRACT(HOUR FROM _Timestamp) <=12
  THEN CAST(EXTRACT(HOUR FROM _Timestamp) AS VARCHAR(2))+':00 AM'
  ELSE IF(EXTRACT(HOUR FROM _Timestamp)-12 < 10
    THEN '0'+CAST(EXTRACT(HOUR FROM _Timestamp)-12 AS VARCHAR(1))+':00 PM'
    ELSE CAST(EXTRACT(HOUR FROM _Timestamp)-12 AS VARCHAR(2))+':00 PM')))
AS RightFormat

However, it won't sort/group correctly

Roy Lambert [Team Elevate]




======= Roy,
Fantastic, way above and beyond the call there.
Thanks a mill again, that's great stuff.
Cheers,
Dar..

Roy,

> errr
>
> <<format the hour into something
> similar to "08:00" or "14:00".>>

I referred to this one:

<<
I need to create a query that produces something similar to :

SnapDate   SnapHour           Count
06/16/09    08:00 AM            100
06/16/09    09:00 AM            23
06/16/09    10:00 AM            32
>>

Anyway a productive discussion with detailed solutions.

Uli

Ulrich

>I referred to this one:

Oh I see - lost interest in the thread after the first post eh

Roy Lambert