|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/7/2009 4:02:02 PM
Posts: 1,
Visits: 7
|
|
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'.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
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
|
|
|
|