SPAM: RE: [Mono-devel-list] DateTime Parameters in MSSQL Server

[Harry Holt]

SQL Server will accept a variety of formats for datetime, but .NET
will often hand it dates it can't deal with due to culture settings. 
DateTime.Now may return '7/13/2005' or '13/7/2005', and SQL is
generally going to reject one or the other.  It will also not accept
dates earlier than January 1, 1753.

Yes, it WILL accept ISO formatted datetime, but not UTC.  It will
generally only accept datetime strings with some type of delimiters
for each part.

A word of caution:
If you are using the SQLHelper methods from
Microsoft.ApplicationBlocks.Data, be careful to never use the
ExecuteDataReader methods that accept connection strings.  Instead,
use the ones that accept a Connection object and manage the connection
in your own code.  The methods that accept a connection string will
open new connections and leave them open indefinately.  You will
eventually run out of connections in the pool and your app will die. 
This was a pretty nasty bug that took me a while to track down,
because it didn't show up until my application went into UAT, and I
was looking for problems with MY code instead of the MS stuff (this
behavior is completely the opposite of their own best practices).

If you're having concurrency issues, don't assume it's mono or your
own code - make sure that the application block is handling things
correctly.

... HH


On 7/13/05, Michael J. Ryan <tracker1_lists at theroughnecks.com> wrote:
> AFAIK, sql server should accept an ISO formatted datetime..
> iirc: yyyy-MM-ddTHH:mm:ss.fff
> not sure about appending a zzz for the offset?
> 
> maybe entering as UTC with a "Z" after the .fff ?
> 
> Chris van Wyk wrote:
> > Hi,
> >
> > Datetime has also been giving me huge amounts of problems.
> >
> > The work around for me was to convert the item using ToString("s"). If you
> > are going to use stored procs, you will need to modify your stored proc
> > parameters in the sql statement to string in stead of datetime.
> >
> > I have also been able to get the Microsoft.ApplicationBlocks.Data going with
> > modification to specific DateTime parameter formatting.
> >
> > There seems to be problems with the data adapter using the sqlhelper from
> > the above. I am getting concurrency errors on updates for instance. If
> > someone else has had concurrency errors please let me know as I have been
> > able to work round this, but I'm not sure if it is a bug in Mono.
> >
> > I am using 1.1.7 and have not tested the above on 1.1.8
> >
> > Regards
> > Chris
> >
> >
> >
> >>-----Original Message-----
> >>From: mono-devel-list-bounces at lists.ximian.com [mailto:mono-devel-list-
> >>bounces at lists.ximian.com] On Behalf Of Hubert FONGARNAND
> >>Sent: 12 July 2005 03:28 PM
> >>To: mono-devel-list at lists.ximian.com
> >>Subject: [Mono-devel-list] DateTime Parameters in MSSQL Server
> >>
> >>I've an issue with datetime parameters with MSSQL Server in Mono...
> >>It seem's that the parameters is badly sent to the SQL Server...
> >>Please test that :
> >>
> >>using System;
> >>using System.Data;
> >>using System.Data.SqlClient;
> >>
> >>class MainClass
> >>{
> >>      static string cnx="user id=sa;password=sa;data
> >>source=10.69.100.93;initial
> >>catalog=Fiche_Produit";
> >>
> >>
> >>      public static void Main(string[] args)
> >>      {
> >>              Console.WriteLine("Hello World!");
> >>              SqlCommand cmd=new SqlCommand();
> >>              cmd.Connection=new SqlConnection(cnx);
> >>              cmd.CommandText="INSERT INTO essais (date) VALUES (@date)";
> >>              cmd.Parameters.Clear();
> >>
> >>      cmd.Parameters.Add("@date",SqlDbType.DateTime).Value=DateTime.Now;
> >>              cmd.Connection.Open();
> >>              cmd.ExecuteNonQuery();
> >>              cmd.Connection.Close();
> >>      }
> >>}
> >>
> >>it returns :
> >>Unhandled Exception: System.Data.SqlClient.SqlException: Erreur de
> >>conversion
> >>du type de données varchar en datetime.
> >>Erreur de conversion du type de données varchar en datetime.
> >>in [0x00034]
> >>(at
> >>/home/hubert/mono/mcs/class/System.Data/System.Data.SqlClient/SqlConnectio
> >>n.cs:266)
> >>System.Data.SqlClient.SqlConnection:ErrorHandler (System.Object sender,
> >>Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e)
> >>in (wrapper delegate-invoke)
> >>System.MulticastDelegate:invoke_void_object_TdsInternalErrorMessageEventAr
> >>gs
> >>(object,Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs)
> >>
> >>
> >>In english : error when converting a varchar datatype into a datetime
> >>
> >>thanks
> 
> --
> Michael J. Ryan - tracker1(at)theroughnecks(dot)com - www.theroughnecks.net
> icq: 4935386  -  AIM/AOL: azTracker1  -  Y!: azTracker1  -  MSN/Win: (email)
> 
> _______________________________________________
> Mono-devel-list mailing list
> Mono-devel-list at lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-devel-list
> 


-- 
====================================================
Robbie the Nanobot says:
"Only YOU can prevent gray goo
(NEVER release nanobot assemblers without replication limiting code)"
http://lizardslounge.org