Teradata and Date Formats for ADO

[fer2485]

I am trying to build a recordset off of a teradata table using ADO. I am having a problem determining the proper syntax for the date field. I have used numerous SQL statements and they all return "Invalid Date". Here are some of the sample select statements i have tried...

SELECT * FROM TABLE WHERE CALL_DATE =select cast('20070801' as date format 'yyyymmdd')

SELECT * FROM TABLE WHERE CALL_DATE={d'2007-08-22'}

SELECT * FROM TABLE WHERE CALL_DATE=date('2007-08-22')

Please help!

 

[bibulous]

Hi, I would use the following

SELECT *
FROM TABLE
WHERE CALL_DATE (DATE) = '2007-08-22'

Ste.

 

[fer2485]

Thank you bibulous!

I tried that and now I am getting a different error...

SELECT *
FROM TABLE
WHERE CALL_DATE (DATE) = '2007-08-22'

"A character string failed conversion to a numeric value"

Any other suggestions or even websites to hunt around? I had browsed onine and found multiple options but none seem to be working...

 

[bibulous]

Hi,

I am not a Teradata expert, but I would suggest trying to add (DATE) to the end of the date string too.

WHERE CALL_DATE (DATE) = '2007-08-22' (DATE)

I have had issues with this kind of thing before. This info is just from my past experiences, so I don't have any websites for you to look at i'm afraid.

Ste.

 

[fer2485]

A character string failed conversion to a numeric value"

Getting the same error...

 

[bibulous]

Well I've been a big help
I don't know if there are any settings that are different, but both formats are valid at my instalation.
I really don't know what to suggest, Sorry I have been of no use & Good luck.

 

[bibulous]

I Lie, one last go

SELECT * FROM TABLE WHERE CALL_DATE = '20070822'

Also seems to work here, but again, whether or not it will help you I don't know.
I can only think it is the way in which the "Dates" are stored / fields defined is different to where I am.
What format does it display the date in if you just select the date for a sample of rows?

 

[fer2485]

That didn;t work either, but i appreciate all the help That is the funny thing, if i do

SELECT * FROM TABLE SAMPLE 10

all i get in return is "invalid date",if i select specific fields from the table, but do not select the date field, i still get "invalid date". Crazy, all i can think of is some setting on the driver, but not sure what to do...

 

[fer2485]

Finally able to get a different error, don;t know if i am making progress or not

SELECT *
FROM TABLE
WHERE call_date=date '2007-Aug-20'

returns invalid date literal