9

How can i search for a particular date for eg: '2013-10-22' from teradata timestamp(6) field?

sel * from table A
where date = '2013-10-22';

I tried the above query which is throwing error. Please help!

RRR
  • 145
  • 1
  • 3
  • 11

3 Answers3

11

You may try like this:-

sel * from table A
where date = date '2013-10-22';

Since in ANSI standard form (must be preceded by the keyword DATE)

Check out this

Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
  • Can i also know how can i group by only with date from timestamp? – RRR Oct 24 '13 at 17:04
  • @RRR:- Didnt get that what you are asking? Could you ask another question! – Rahul Tripathi Oct 24 '13 at 17:06
  • If i want to group by with respect to timestamp column, only wrt date. for eg: when i perform group by createddate, it gives all records separately because the time in each value is different. i want to group all similar dates together irrespective of the time in the timestamp fields – RRR Oct 24 '13 at 17:07
  • @RRR:- I dont think so that is possible however you can group by on your date column! – Rahul Tripathi Oct 24 '13 at 17:09
1

And more formally:

select * 
from table A
where cast(timestamp_column as date) = date '2013-10-22';

I'm guessing that you were just showing an example, because I don't think you can have a column named date; it's a reserved word. The keyword "date" above is how you specific an ANSI date constant and is not related to the "date" function.

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
  • The unexpected thing is: you don't even have to do the CAST. When a Timestamp is compared to a Date it is automatically casted to a date. I don't know if this is based on Standard SQL, but when i investigated a supposed bug i found this defined in the manuals. – dnoeth Oct 22 '13 at 18:34
  • Using functions in the where clause like this tends to slow down production. This is magnified if you are applying the function to an indexed field. – Dan Bracuk Oct 22 '13 at 19:44
  • @dnoeth Yes, I sort of remember that, but I hate writing code that uses default conversions. Same reason why I always use the `date` modifier in front of a date string. Its not really needed but it makes it much clearer to me what is going on. – BellevueBob Oct 22 '13 at 23:18
  • If i want to group by with respect to timestamp column, only wrt date. for eg: when i perform group by createddate, it gives all records separately because the time in each value is different. i want to group all similar dates together irrespective of the time in the timestamp fields – RRR Oct 24 '13 at 17:10
  • @RRR I think your "group by" thing deserves a separate question. Ask another, and be sure to show an example of what you want. Saying "group by with respect to timestamp" does not make much sense. – BellevueBob Oct 24 '13 at 23:47
0

Something like this:

where YourTimestampField >= {d '2013-10-22'}
and YourTimestampField < {d '2013-10-23'}
Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
  • If i want to group by with respect to timestamp column, only wrt date. for eg: when i perform group by createddate, it gives all records separately because the time in each value is different. i want to group all similar dates together irrespective of the time in the timestamp fields – RRR Oct 24 '13 at 17:09