I'd like to be able to do something like this:
insert into mydb.mytable (updatetimestamp) values (#1/15/2012 01:03:00#)
...or...
select * from mydb.mytable where updatetimestamp = #1/15/2012 01:03:00#
Using literals wouldn't required the longwindedness of casting and whatnot since it would immediately interpret the expression as a DATE or TIMESTAMP.
Does Teradata support this type of syntax?
Yes, Teradata supports the ANSI format for dates and timestamps. Reference: http://www.teradataforum.com/l070316a.htm
For example:
INSERT
INTO mydb.mytable (updatetimestamp)
VALUES (TIMESTAMP '2012-01-15 01:03:00');
Or:
SELECT *
FROM mydb.mytable
WHERE updatedate = DATE '2012-01-15';