Toad World® Forums

Extracting hour from a time stamp

Hello,

I’m trying to extract the hour from a time stamp that’s formatted as MM/DD/YY HH:MM TT.

I need to set a pivot table up to where it breaks it down by hour and currently when I try to use the timestamp variable it can only break it down to days. I’m coming from another program where that work just fine but I couldn’t figure out how to take off the autogrouping in TDP because if you uncheck all boxes it just checks the one you previously were summing down to by default.

So I’ve looked into other means but haven’t had any luck using extract or things of that nature. Usually ends up in some kind of error line like “invalid extract…”

Hoping someone can give me an example line of code that would solve the issue.

Thanks!

Also unrelated issue since I’m posting on the forums anyways. Is there a reason why in select statements it doesn’t like to accept case when statements like this:

Case When Example.Food in ((‘apple’),(‘lemon’),(‘orange’)) Then ‘Fruit’ …etc. This usually returns a missing right parenthesis error whenever I try to visualize the statement in the query builder.

The only time I can get the case statements to work is when I incorporate = / or commands like this:

Case When Example.Food = ‘apple’ OR Example.Food = ‘lemon’ OR Example.Food = ‘orange’ THEN ‘Fruit’ …etc

Why don’t you use Select datepart(hh, yourDatefield) as Hr in your sql to set the variable for hours (SQL Server). Or for Oracle use Extract (Select Extract(Hour From YourDateField or To_Date function) as Hr From Dual.