Date Range Syntax

UDA

Date Range Syntax

I have a syntax question on Teradata date ranges. I Have the following SQL, and am getting the following error. I am used to working with oracle, but couldn't find the needed syntax for this.

Select * from db.table WHERE CREATED BETWEEN TO_DATE ('6/2/2009 10:11:06 PM','mm/dd/yyyy hh:mi:ss am') AND TO_DATE('8/31/2009 10:11:06 PM', 'mm/dd/yyyy hh:mi:ss am') Order By ROW_ID

ERROR [42000] [NCR][ODBC Teradata Driver][Teradata Database] Syntax error: expected something between '(' and the string '6'.
1 REPLY
Enthusiast

Re: Date Range Syntax

Two ways:

1. Have someone install the Oracle UDF's on Teradata - then it will work!

OR

2. If you want to go Teradata native, it is:

Select * from db.table
WHERE Created BETWEEN
('06/02/2009 10:11:06 PM' (TimeStamp,Format 'dd/mm/yyyybhh:mi:ssbT'))
AND ('31/10/2009 10:11:06 PM' (TimeStamp,Format 'dd/mm/yyyybhh:mi:ssbT'))
Order By ??;

Three points:
1.On Teradata, a date field is what it says on the tin! IE it is a date, not a date and time. The To_Date function returns a timestamp, not a date - so it is compatible with the original Oracle function.

2. If you say the date has a format 'dd/mm/yyyy', it needs 2 digit day and month number. So 06/02/2009. 6/2/2009 is not allowed. To-Date allows 6/2/2009 for compatibility.

3. I dont know why you would sort by Rowid, but you cannot on Teradata.

See the SQL Ref - Data Types and Literals for full description of the Format clause. If you dont have it, you can download from Teradata.com.

HTH