INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Teradata and Date Formats for ADO

Teradata and Date Formats for ADO

(OP)
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!

RE: Teradata and Date Formats for ADO

Hi, I would use the following

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

Ste.

RE: Teradata and Date Formats for ADO

(OP)
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...

RE: Teradata and Date Formats for ADO

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.

RE: Teradata and Date Formats for ADO

(OP)
"A character string failed conversion to a numeric value"

Getting the same error...

RE: Teradata and Date Formats for ADO

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.

RE: Teradata and Date Formats for ADO

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?

RE: Teradata and Date Formats for ADO

(OP)
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...

RE: Teradata and Date Formats for ADO

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close