Between Times not working

Database
Enthusiast

Between Times not working

I get error someting between a string or a Unicode character literal and the 'and' keyword. 

SELECT CREAT_TM,

Case

CREAT_TM

When ASKME_CREAT_TM Between ('08:00:00' and '12:00:00') Then 'AM'

Else 'unknown'

END AS TimeofDAY,

FROM Table

Where (ASKME_CREAT_DT) Between '2012-08-01' AND '2012-08-02'

AND CMTMT_DT = CREAT_DT

7 REPLIES
WAQ
Enthusiast

Re: Between Times not working

The CASE syntax in your given query is not correct. It should be like this:

Case When ASKME_CREAT_DT Between ('2012/08/29') and ('2012/08/30') Then 'AM'

    Else 'unknown'

END AS TimeofDAY

Supporter

Re: Between Times not working

Your case is odd as you mix two syntax types, so it will not work.

What data type is ASKME_CREAT_TM?

Supporter

Re: Between Times not working

Select current_timestamp(0) cts,
cast(cts as time) as ctime,
case when ctime between cast('08:00:00' as time) and cast('12:00:00' as time) then 'AM' else 'unkown' end
;

Enthusiast

Re: Between Times not working

ASKME_CREAT_TM is a [TIME(0)] field. 

 I had posted the syntax wrong and corrected it below.    I can do CASE When AskME_CREAT_TM  09:00:00 for example, and it will show AM on any of the records where that is the time in that field.   But it keeps giving error for the Between times statement.

All I'm wanting to do is show 'AM" in a colum when the time is between certain times in the ASKME_CREAT_TM field, e.g. 8-12 = AM.  And eventually 'PM' 'Late PM' etc.    . 

 I'm new to Teradata.  Thanks.   

SELECT ASKME_CREAT_TM,

Case

CREAT_TM

When ASKME_CREAT_TM Between ('08:00:00' and '12:00:00') Then 'AM'

Else 'unknown'

END AS TimeofDAY,

FROM Table

Where (ASKME_CREAT_DT) Between '2012-08-01' AND '2012-08-02'

AND CMTMT_DT = CREAT_DT

Enthusiast

Re: Between Times not working

Ulrich, 

I got it to work as this.  Thanks for your help.  

SELECT ASKME_CREAT_TM,

cast(ASKME_CREAT_TM as time) ,

case when ASKME_CREAT_TM between cast('08:00:00' as time) and cast('12:00:00' as time) then 'AM' else 'unkown' end,

WAQ
Enthusiast

Re: Between Times not working

As mentioned by Ulrich you need to cast the time to make it time (type) before comparing it.

Similarly, you also need to do the same with your dates in WHERE clause (assuming ASKME_CREAT_DT is of DATE type).

Supporter

Re: Between Times not working

case when cast(ASKME_CREAT_TM as time) between cast('08:00:00' as time) and cast('12:00:00' as time) then 'AM' else 'unkown' end,

should work as well, so no need for the additional field.

Might be worth to create a SQL UDF out of this - as you would be able to reuse the code easier.