Date Format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date Format

Author

Topic

wd7179
Starting Member

10 Posts

Posted - 2006-08-14 : 08:52:26

Can somebidy help with this date issue? I have been trying everything, and looking through the forums but, I have not found a solution yet.

I have a table in Teradata that I am trying to import. This table has a date column, that returns '9999-12-31 23:59:59.000'.

When I run the openquery from SQL Analyzer, and output to text I get the data back just fine but, when I try to use a grid it returns this error message: Error converting data type DBTYPE_DBTIMESTAMP to datetime.

The settings for the SQL Server are: us_english mdy
The server settings are: MM-dd-yyyy

Thanks for any help you can give.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 09:25:53

try these - they might give a clue as to what to do

select convert(datetime,'9999-12-31 23:59:59.000')
select convert(datetime,'9999-12-31 23:59:59.000',120)
set dateformat dmy
select convert(datetime,'9999-12-31 23:59:59.000')
set dateformat mdy
select convert(datetime,'9999-12-31 23:59:59.000')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wd7179
Starting Member

10 Posts

Posted - 2006-08-14 : 11:42:25

I tried the samples you provided, and the results are as follows:

select convert(datetime,'9999-12-31 23:59:59.000')- ok
select convert(datetime,'9999-12-31 23:59:59.000',120)- ok
[set dateformat dmy
select convert(datetime,'9999-12-31 23:59:59.000')] - out of range
[set dateformat ymd
select convert(datetime,'9999-12-31 23:59:59.000')] - ok

But no matter what I do with the dateformat, when I run the query in SQL Analayzer and have it returned to a grid, it still fails with the original error.

I am submitting a query to Teradata through OpenQuery, the Teradata driver is version 3.03, which is our latest, and then the link is setup using the MS OLEDB for ODBC.

Another client has the SQL Server Personal edition, and it runs just fine on his PC, so I know the data is ok.

wd7179
Starting Member

10 Posts

Posted - 2006-08-14 : 11:44:01

Sorry, I also can get it to work by using the CAST in the Teradata SQL Statement but, the client does not want to list out each column, they want to bring back the whole table via the Select *.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 12:07:28

What are you trying to do?
You say you want to import the data - I guess into sql server.
Then import that into a grid control?

Is the import into sql server ok and the grid control failing?

Openquery will use the sql server service profile whereas the grid control will usethe clients.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wd7179
Starting Member

10 Posts

Posted - 2006-08-14 : 12:53:20

I am just trying to get the Select From OpenQuery() to return to a grid inside of QUery Analyzer. If I set Query analyzer to return results in text it works ok, but if I set it to return results to a grid it fails.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-08-14 : 13:23:53

Sorry - I was being a bit dense.

Odd - I rarely use the grid so don't have this problem.
I take it that it fails for all dates later than the 12th of the month?
i.e. it's because it's getting confused between day and month?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

wd7179
Starting Member

10 Posts

Posted - 2006-08-14 : 14:23:39

I changed the server setting to format like 'yyyy,mm,dd'
and I still get the error. The real brain teaser is that the client has a Personal addition that will run it no problem. I can pull back certain records but, not all of them. Which leads me to believe that it is bad data on the source system, but I still wonder why it will run on the other server.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-15 : 03:32:05

Try to import the dates without the "-" betweeen year/month and month/day, to make the date universal.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources