Converting Char to Date Type Error

[gagslondon]

Hello,

I too have a date problem with SQL Server 2000 but a search of this forum reveals mine is peculiar. Here are the detilas.

Using ASP I establish the date:

strInvoiceDate = Date()
strInvoiceDate = FormatDateTime(strInvoiceDate,2)
strInvoiceDate = CDate(strInvoiceDate)

After INSERT we were always getting 01/01/1900 in the table regardless of the value of strInvoiceDate.

To fix this I adapted my INSERT statement to include:

(Convert(Datetime,'" & strInvoiceDate & "',103))

This worked, for a while. Then it stopped working. After some testing I could get things working with

(Convert(Datetime,'" & strInvoiceDate & "',101)) on our local test database and 103 on the web server. I never figured out why it would stop or start working again.

Today I get an error similiar to the subject line of this post when I am connected to the web server in some other way - PcAnywhere or Using the Enterprise Manager. We battle to succesfully recreate this error as the criteria seems to change constantly. Sometime Convert 101 breaks, sometimes 103. Sometimes connecting with both pcAnywhere and The Enteprise Manager and sometimes only one.

All our machines use Win 2k (Professional or Server).

We are really stuck now and receiving very little sympathy from our client. Any ideas?

I am beginning to believe the solution is spelt MySQL.

Thanks in advance.

gags

 

[rajeevnandanmishra]

Hi there,
I have not worked with SQL 2K. But have faced problem something like this only. What i had done is to send the Date in the backend only in yyyy/mm/dd format. That is by converting date into this format in front-end only. It does not require using the CONVERT function in back-end. It helped us. Hope it will help you also.

Moreover, If you have a static date format(i.e suppose you always insert records in mm/dd/yy format), then you can use "SET DATEFORMAT mdy " command to set the dateformat of SQL server.