Trouble with dates after reinstalling MSSQL

[RH+]

I have one project running on a DB on MSSQL 2k, with other *big* 3rd party software.
2 days ago they have a problem with MSSQL, and for some reason instead of looking for the problem they installed MSSQL on top the previous version, restore the backups of the 2 DBs and start using it again.
The other software works ok, my project has problems with dates
Example code:

Dim fechaInicio As New SqlClient.SqlParameter("@FechaInicio", SqlDbType.DateTime)
Dim fecha1 as string

fechaInicio.Value = Format(DTPInicio.Value, "yyyy/MM/dd")
fecha1 = Format(DTPInicio.Value, "dd/MM/yyyy")

        sql = "Select Sini from saldos where fecha='" & fechaInicio.Value & "' and IDsucursal=" & IDSucursal.Value
        InicialBanco = db.GetScalar(sql)

All queries I made using the data of the backup (with date <=30/08/2010) trow "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
And I can do queries of data newer than the backup.

I checked visually the data of the DB, and all seems to be ok.
To do the queries for dates <=30/08/2010 I need to use in the SQLStatement "fecha1" instead of "fechaInicio.Value".

For data inserted after 1/09/2010 I still have some dates problem, I think that if I solve the previous problem the others will be solved too.

The code posted was worked perfectly for months.

I know I can improve the way I make the queries, but for now I need something to make this project run again while I continue improving it.
Thanks a LOT

[dglienna]

Is that a DateTime field?

[RH+]

Is that a DateTime field?

Yes

[viperbyte]

What I would do is take a sample of records and step throught the code and inspect the values once I found a record that cause the program to choke. Then see what is sooo different about that record and the one before it that was succesful. There has to be something different about it. Play around with trying to use the values and see what you get in msgbox, debug.print; if everything else doesn't give a clue. Was SQL Server upgraded to 2005 or 2008 from 2000?-just out of curiosity.

[RH+]

What I would do is take a sample of records and step throught the code and inspect the values once I found a record that cause the program to choke. Then see what is sooo different about that record and the one before it that was succesful. There has to be something different about it. Play around with trying to use the values and see what you get in msgbox, debug.print; if everything else doesn't give a clue. Was SQL Server upgraded to 2005 or 2008 from 2000?-just out of curiosity.

The problem seems to be that it needs now the dates in other format:
dd/mm/yyyy instead of YYYY/mm/dd...for the type of application I have dates everywhere, SPs, lots of queries, etc. I figured that out using msgbox and debug.print
The reinstallation was 2000->2000, same version

[viperbyte]

Hi there. I did some investigating and several sources associate that error with a discrpency with regional settings. The bit that I copied and paste in this message mentions that default language is English for SQL Server installations. Maybe that's the way it is now in your newly installed instance of SQL Server and maybe it wasn't like that before. Or maybe a change in regional settings from the PC the application is sitting on? Take a look at this:

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

This is due to an inconsistency in the language settings between the web server and SQL server (or SQL server user). By default, SQL Server users are set to "us_english" as the default language. The result is that different date formats are being used and are therefore not recognized by the database.

There are several options to remedy this situation:

1. Change the SQL user's default language to match the language settings on the SQL server.
To do this, open SQL enterprise manager and connect to your database server. Expand Security -> Logins and open the properties dialog for the user that you are using to connect to the database. On the main properties page, change the language to equal the same language that is set on your web server.

2. Modify your connection string
On your SQL connection string, add a parameter of Language= and set it equal to the correct language. For example, Language=British.

[RH+]

Thanks viperbyte, I'll try that tomorrow.
I Just look and see that they have change the collation too...I'll try resetting that to the previous value.
I can change anything of my DB but not a lot of the server itself (because the other software is working and if I broke it they will kill me =P) But I'll try the things you said.
Interesting the thing of the connection string, didnt know that.
Any other idea will be well received
Thanks!

[TechGnome]

The database maybe looking at the dates in a mm/dd/yyyy format... you're supplying it in dd/mm/yyyy format... To get around this, I usually use the ISO format of yyyy-mm-dd in that format, I've never had it fail me.

-tg

[RH+]

The database maybe looking at the dates in a mm/dd/yyyy format... you're supplying it in dd/mm/yyyy format... To get around this, I usually use the ISO format of yyyy-mm-dd in that format, I've never had it fail me.

I'm using yyyy/mm/dd:

fechaInicio.Value = Format(DTPInicio.Value, "yyyy/MM/dd")

[TechGnome]

use hyphens... not whacks...

-tg

[RH+]

use hyphens... not whacks...

Before the re-installation and in all my projects it works with "/", I can edit all the SQLs statements, but Im trying to know what have change in the SQL server, and need to show that they triggered the problem and not me. I need to show them that the problem appears with the rinstallation (Some of them already know that, but i dont want to adapt my project every time they want to "play" with the server).
Need to tell them something like "you set the collation wrong on install" or the language, etc.
thanks!

[dglienna]

I'd contact MS Support. They have plenty of SQL Experts, that can help with almost ANY situation. If you get one incident FREE per year, you can eat the expense. Otherwise, have the client agree to it, stating that 'if it is your apps fault, youll eat the fee'.

Doesn't sound like you'll have to pay the $400

[RH+]

I'd contact MS Support. They have plenty of SQL Experts, that can help with almost ANY situation. If you get one incident FREE per year, you can eat the expense. Otherwise, have the client agree to it, stating that 'if it is your apps fault, youll eat the fee'.

Doesn't sound like you'll have to pay the $400

Today they give me admin access for 5 minutes...they created the user and set "spanish" for language...it should be "English", corrected that. All working as it should.
Obviously they have to pay for that...
Thanks to all =)

[dglienna]

Tell him a 'pro' gave the suggestion, and split the difference!