DateTime Error Following DTS

[PieMan]

I have an application developed in VB6 that uses SQL Server 7.
I've copied the DATA from my SQL Server database into another BLANK database on the same SQL Server(7)using the DTS within Enterprise Manager (SQL Server 2000).

The application now returns error -2147721913 (invalid datetime to char conversion) when run against either of the two databases.

I can format the dates in my source code so that it works, but I really need to know the reason why it happened and how I can reverse the effect.

Cheers

Pieman

 

[tlbroadbent]

You didn't tell us the date format you use. Typically, programmers want to use a date format like '10/4/01' which SQL Server will interpret as 'Oct 4 2001'. If the programmer happens to use British or French date notation, she really wants to store 'Apr 10 2001.' SQL Server doesn't work that way, it will always use the American format unless instructed differently.

You can set the format for entering SQL Server date values with one of the following statments. Change the parameters to meet your needs. You can also use the CONVERT function (see SQL BOL).

Set dateformat dmy
SET LANGUAGE british

You can only set a language if it is installed on your SQL Server.

I've almost always used the 'mmm dd yyyy' format in SQL Server. It is never ambiguous. Some prefer the 'dd-mmm-yyyy' format.

Read more about using dates from a programmer's perspective.

http://www.dougdean.com/How2Project6.htm

http://www.4guysfromrolla.com/webtech/041001-1.shtml

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.