Error compare times

Database

Error compare times

Hi guys,

Anyone know tell me why this works: 

SELECT CASE

WHEN CAST('20:00:00' AS TIME) < CAST('16:00:00' AS TIME) THEN 1

ELSE 0

END

and it no:

SELECT CASE

WHEN CAST('21:00:00' AS TIME) < CAST('16:00:00' AS TIME) THEN 1

ELSE 0

END

Thanks!

4 REPLIES

Re: Error compare times

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CASE
 WHEN CAST('20:00:00' AS TIME) < CAST('16:00:00' AS TIME) THEN 1
 ELSE 0
 END;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

<CASE  expression>
------------------
                 0

 BTEQ -- Enter your SQL request or BTEQ command:
SELECT CASE
 WHEN CAST('21:00:00' AS TIME) < CAST('16:00:00' AS TIME) THEN 1
 ELSE 0
 END;

 *** Query completed. One row found. One column returned.
 *** Total elapsed time was 1 second.

<CASE  expression>
------------------
                 0

Hi.

What is it that does not work?

Cheers

Carlos.

Re: Error compare times

For me , the second option returns 1 rather than 0. I am in Brazil, may have influence on the result?

Thanks Carlos!

N/A

Re: Error compare times

location shouldn't affect that.  Time is time no matter where you are.

What version of the database are you running?   I see the same results as what Carlos is showing.

Can you run a select and output the results of the cast, that might help figure out where the problem is.

Teradata Employee

Re: Error compare times

TIME is stored internally at UTC timezone. That suggests that your system or session timezone is not at UTC so a timezone adjustment is being made.  21:00 is likely wrapping around to 00:00 which then compares less.

I would put those casts and some others in the Select list to see what the result of the cast is. And review your system and session timezone settings.