pre-1750 (#?!) dates in SQL table

Sorry for incomplete info here. This is infrequent problem we have.
We have SQL 2000 (sp3?). Our internet team inserts rows into
a table. Don't know the mechanism they use (yet). We use a
VB6 app to read the rows. Two rows have appeared in the table
with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
for year. When I did SELECT CONVERT (float, colname) I got
-61024.0 (or some value pretty close to that).
Has anyone see anything similar?
Craig HesselI've never seen anything like it. I'd start with DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:f39je.4580$eR.285@.fe05.lga..
.
> Sorry for incomplete info here. This is infrequent problem we have.
> We have SQL 2000 (sp3?). Our internet team inserts rows into
> a table. Don't know the mechanism they use (yet). We use a
> VB6 app to read the rows. Two rows have appeared in the table
> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
> for year. When I did SELECT CONVERT (float, colname) I got
> -61024.0 (or some value pretty close to that).
> Has anyone see anything similar?
> Craig Hessel
>
>|||Thanks, Tibor. I contacted internet team member and he confirmed they could
indeed insert bad dates:
"I just ran a test and it looks like the SQL driver we use from WebSphere
does not do the same date checks as the one you use. I was able to enter a
date in the year 204 and the database took it without question. "
"We use JDBC drivers to interact with the database and we use Java code to
come up with what essentially is a Long value representing the number of
milliseconds since 1/1/1900 (or something like that) to represent the date.
We then update the database with that value. "
BTW, I missed a digit in the SELECT CONVERT(float, colname) value reported
earlier. The year
204 is about 600,000 days, not 60,000 days, before Jan 1, 1900.
Craig
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PrRtBRXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I've never seen anything like it. I'd start with DBCC CHECKTABLE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Craig Hessel" <craig_hessel@.hotmail.com> wrote in message
> news:f39je.4580$eR.285@.fe05.lga...
>|||I've seen a similar problem with bulk loads of dates using non-MS ODBC
drivers. Do check your SP though as I don't recall this happening since
SP3. Check for a driver update as well.
You may find you can fix the dates with an UPDATE like this:
UPDATE YourTable SET dt = NULL
WHERE dt < '17530101'
David Portas
SQL Server MVP
--sql

pre-1750 (#?!) dates in SQL table

Sorry for incomplete info here. This is infrequent problem we have.
We have SQL 2000 (sp3?). Our internet team inserts rows into
a table. Don't know the mechanism they use (yet). We use a
VB6 app to read the rows. Two rows have appeared in the table
with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
for year. When I did SELECT CONVERT (float, colname) I got
-61024.0 (or some value pretty close to that).
Has anyone see anything similar?
Craig Hessel
I've never seen anything like it. I'd start with DBCC CHECKTABLE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:f39je.4580$eR.285@.fe05.lga...
> Sorry for incomplete info here. This is infrequent problem we have.
> We have SQL 2000 (sp3?). Our internet team inserts rows into
> a table. Don't know the mechanism they use (yet). We use a
> VB6 app to read the rows. Two rows have appeared in the table
> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
> for year. When I did SELECT CONVERT (float, colname) I got
> -61024.0 (or some value pretty close to that).
> Has anyone see anything similar?
> Craig Hessel
>
>
|||Thanks, Tibor. I contacted internet team member and he confirmed they could
indeed insert bad dates:
"I just ran a test and it looks like the SQL driver we use from WebSphere
does not do the same date checks as the one you use. I was able to enter a
date in the year 204 and the database took it without question. "
"We use JDBC drivers to interact with the database and we use Java code to
come up with what essentially is a Long value representing the number of
milliseconds since 1/1/1900 (or something like that) to represent the date.
We then update the database with that value. "
BTW, I missed a digit in the SELECT CONVERT(float, colname) value reported
earlier. The year
204 is about 600,000 days, not 60,000 days, before Jan 1, 1900.
Craig
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PrRtBRXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I've never seen anything like it. I'd start with DBCC CHECKTABLE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Craig Hessel" <craig_hessel@.hotmail.com> wrote in message
> news:f39je.4580$eR.285@.fe05.lga...
>
|||I've seen a similar problem with bulk loads of dates using non-MS ODBC
drivers. Do check your SP though as I don't recall this happening since
SP3. Check for a driver update as well.
You may find you can fix the dates with an UPDATE like this:
UPDATE YourTable SET dt = NULL
WHERE dt < '17530101'
David Portas
SQL Server MVP

pre-1750 (#?!) dates in SQL table

Sorry for incomplete info here. This is infrequent problem we have.
We have SQL 2000 (sp3?). Our internet team inserts rows into
a table. Don't know the mechanism they use (yet). We use a
VB6 app to read the rows. Two rows have appeared in the table
with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
for year. When I did SELECT CONVERT (float, colname) I got
-61024.0 (or some value pretty close to that).
Has anyone see anything similar?
Craig HesselI've never seen anything like it. I'd start with DBCC CHECKTABLE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Craig Hessel" <craig_hessel@.hotmail.com> wrote in message news:f39je.4580$eR.285@.fe05.lga...
> Sorry for incomplete info here. This is infrequent problem we have.
> We have SQL 2000 (sp3?). Our internet team inserts rows into
> a table. Don't know the mechanism they use (yet). We use a
> VB6 app to read the rows. Two rows have appeared in the table
> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
> for year. When I did SELECT CONVERT (float, colname) I got
> -61024.0 (or some value pretty close to that).
> Has anyone see anything similar?
> Craig Hessel
>
>|||Thanks, Tibor. I contacted internet team member and he confirmed they could
indeed insert bad dates:
"I just ran a test and it looks like the SQL driver we use from WebSphere
does not do the same date checks as the one you use. I was able to enter a
date in the year 204 and the database took it without question. "
"We use JDBC drivers to interact with the database and we use Java code to
come up with what essentially is a Long value representing the number of
milliseconds since 1/1/1900 (or something like that) to represent the date.
We then update the database with that value. "
BTW, I missed a digit in the SELECT CONVERT(float, colname) value reported
earlier. The year
204 is about 600,000 days, not 60,000 days, before Jan 1, 1900.
Craig
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23PrRtBRXFHA.3716@.TK2MSFTNGP12.phx.gbl...
> I've never seen anything like it. I'd start with DBCC CHECKTABLE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Craig Hessel" <craig_hessel@.hotmail.com> wrote in message
> news:f39je.4580$eR.285@.fe05.lga...
>> Sorry for incomplete info here. This is infrequent problem we have.
>> We have SQL 2000 (sp3?). Our internet team inserts rows into
>> a table. Don't know the mechanism they use (yet). We use a
>> VB6 app to read the rows. Two rows have appeared in the table
>> with pre-1750 dates in last month. E.g., 0204-03-02 00:00:00. When
>> I did SELECT DATEPART(year, colname) in Query Analyzer, I got 204
>> for year. When I did SELECT CONVERT (float, colname) I got
>> -61024.0 (or some value pretty close to that).
>> Has anyone see anything similar?
>> Craig Hessel
>>
>|||I've seen a similar problem with bulk loads of dates using non-MS ODBC
drivers. Do check your SP though as I don't recall this happening since
SP3. Check for a driver update as well.
You may find you can fix the dates with an UPDATE like this:
UPDATE YourTable SET dt = NULL
WHERE dt < '17530101'
--
David Portas
SQL Server MVP
--

Pound Sign Problem

Hi

I have come across a bit of problem with my Web Form. I have standard textbox which inserts a value into a SQL 2000 database. However if I put a pound sign anywhere inside the text box and do the insert it disappears when I try and reshow the value.

I have checked the database field to see if it has been inputted but its no where to be seen.

So what has happened to my pound sign? any ideas?

I am using VS.Net 2003.

Thanks in advanceThat is strange. SQL 2000 does not reject the pound sign. Neither does the text box control. I did several tests, cannot seem to produce your problem. Do you do any kind of massage to your input in your code before it is insert into the database table?

Please post your code.|||Hiya

Sorry for the delay in getting back to you, please find below my code. This problem really does have me miffed

Cmd As New SqlCommand

Conn.Open()
With objCmd
.Connection = Conn
.CommandType = CommandType.Text
.CommandText = "INSERT INTO PhoneList (FirstName, Surname) " & _
" VALUES (@.FirstName, @.Surname)"

.Parameters.Clear()
.Parameters.Add("@.FirstName", SqlDbType.VarChar).Value = txtFirstName.Text
.Parameters.Add("@.Surname", SqlDbType.VarChar).Value = txtSurname.Text
.ExecuteNonQuery()
End With
Conn.Close()

Field types in theSQL Server 2000 DB are Varchar's. Just thought could this be one of the rasons for the roblem

Thanks in advance|||I have seen this problem before and I think the DataType on the column needs to beText to store ASCII characters.|||Hi PD

I have changed the data type to Text in my SQL DB but I still get the same problem

It just seems really odd, every other character works including !"$%^&*()

but the £ just does not get stored|||I was sleeping when I read your post, I thought you meant the # sign.

Themonetary data types MONEY and SMALLMONEY can accept many national currency symbols like the British pound, the Euro and others.|||How about if you use NVarChar instead?
Although you shouldn't have to go unicode in order to save the pound sign...

Regards
Fredrik|||Hi Fredrik

I have tried changing it to NvarChar and still didnt have any joy. I have even searched around on teh web and cant seem to find anybody else who has had that problem

Just wondering can anybody else replicate this problem there end?

I dont seem to have any hair left..... i think ive pulled it all out|||OK the only way I can seem to get round the problem is by inputting the HTML equvilant of a £ sign which is £

So when this is rendered it appears as a £

But im sure this cant be a solution.... can it?

I even tried to set validateRequest="false", but did not have any joy|||I am sorry. I thought it was # as well.
I did a test. Cannot re-produce the problem. I am using Varchar for the data field.

Did you try to debug and see whether the pound value was passed in correctly?|||Here is my test code in C#. I just have a textbox at the front end.

conn.Open ();
string strInsert = "Insert Into Chun (Test) Values (@.chun)";
SqlCommand cmd = new SqlCommand (strInsert, conn);
SqlParameter p = new SqlParameter("@.chun",SqlDbType.VarChar,50);
p.Value = txtPound.Text;
p.Direction = ParameterDirection.Input;
cmd.Parameters.Add (p);
cmd.ExecuteNonQuery ();
conn.Close ();

Here is the value I entered in the text box:

£5432

The value is stored and retrieve displayed as it is.|||I have a similar issue when I pass the # as part of a query string! It chops everything off after the pound sign!|||I did eventually manage to solve my pound sign problem, it was a problem with the globalization setting in the web.config.

Try reading the below article and see if it helps

http://www.howtodothings.com/showarticle.asp?article=755

Good luck