my dbms transforms my dates 1

[StylizIT]

Hi all,

i 've got a real strange problem.
I 'v got an asp.net/vb.net application and a mssql db at the end. I have a form where I can insert some info in the tables. Everything went well for a couple of months, but now my dates transform on a real strange way. no matter wich date I give up, those date's never reach the db.
example: date = 31-12-2004(Europe date) --> in the db the date is
21-07-1894
and I've seen the other dates, the month and the year are the same, they all show me july 1894.

Has everyone ever seen this before.

PS. I'm using win2000 service pack 4 with mssql 2000 (no service pack)

You can better regret that you have done something than regret the fact you done nothing

 

[StylizIT]

Where The experts at???

You can better regret that you have done something than regret the fact you done nothing

 

[donutman]

Well, first you have to figure out, for certain, where the problem is. Is it the front-end or back-end? You've posted in a SQL forum, so that implies that you've already proved it is a SQL problem?
It's a reasonable guess, however, that it is the app, so verify what you're passing to the db. Then try an update on the server directly, if necessary.
BTW, there is a sp3 for SQL Server.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLBill]

And SP3a for SQL Server definitely needs to be applied. I suggest applying it now.

-SQLBill

 

[ESquared]

Assuming the dates are truly munged in the database and it's not just a display problem, your data may still be salveagable, but you might have to convert it all to a number format (real if you don't mind a tiny loss of a few milliseconds of precision, binary otherwise) then do a custom transformation to turn them back into 'correct' datetime values.

Give me 10 widely varying examples of the bad dates in the following format and I'll see what I can do to help:

[bode]SELECT convert(binary(8),YourDateField) BinVal, YourDateField[/code]

And add a column with what datetime each one SHOULD be.

 

[donutman]

Say, Bill, MS says that you don't have to apply 3a if you have the previous version of SP3 installed. Yet the knowledge base article about the memory leaks with ODBC drivers, suggests that you definitely should be installing 3a. What's your take on that?
Also if my workstation is running just EM and not a local instance of the engine, do I still need to install the sql2kdesksp3?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

bode, code, it's all the same thing

 

[SQLSister]

Esquared, clearly it isn't since the code window didn;t display. But I certainly can't laugh at anyone else's typing. We all know how rotten mine is.

 

[ESquared]

Thanks for not laughing, a star for you!

 

[SQLBill]

Karl,

Off topic, but since you asked in this thread:

This is the only thing SP3a does that SP3 doesn't do:

SQL Server 2000 SP3a:

Improves serviceability for SQL Server 2000 Evaluation Edition. SP3a enables service pack upgrades for trial software. In the past, service packs have not been applicable to trial software.

Includes a new version of Microsoft Data Access Components (MDAC) version 2.71a. This version fixes the installation of MDAC files and a memory leak. These issues are addressed in articles 814572 and 814410 in the Microsoft Knowledge Base.

Disables listening on port 1434 when networking is disabled. This provides further protection from "denial of service" attacks.

The part about MDAC lists two other articles with solutions and you can download the latest MDAC by itself which will also solve the issue.

As for applying the SP to a remote machine with only the client tools? That's interesting - I've thought about that also. But the Readme file says indicate that it's only for the database - it talks about stopping the services and that it has to be run on each instance.

I have been searching MS's site for updates to Enterprise Manager, but I don't find any. However, MS does issue updates to it's Books Online separately from the SPs (they are also in the SPs, but you can get it separate to update your remote/client machines). So, I don't think the SP will run on the client side.

-SQLBill

 

[StylizIT]

sorry guys for my disapaering lately,
I was working on another project.

The problem has been solved, the dates were like this:
insert into tb1(date1)values(01-01-2004)
this gives problems.
now they are inserted like this:
insert into tb1(date1)values('01-01-2004')

I only don't understand 1 thing it has been working correct for the past months since it was implemented, why does it give these problems now after several months??

thnx anyway, this worx for my now and I'm happy

You can better regret that you have done something than regret the fact you done nothing

 

[ESquared]

I'm surprised it ever worked. Did you install a service pack lately?