×
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!
  • Students Click Here

*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.

Students Click Here

Jobs

Teradata and Date Formats for ADO

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!

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