• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

SQL 2005 dates

Chaps, need some help with this one ASAP

I need to pass through a datetime value into a SQL 2005 sp.

exec usp_tblCarrier_upd @CarrierID=N'2',@CarrierCode=N'LTR06',@CarrierDesc=N'Letterhead Carrier
test',@ProductID=2,@ClientID=2,@AuditUserID=N'WACSL-nc4020\Administrator',@AuditDate='Jul 24 2006 10:02:35:530AM',@TimeStamp='Jun 15 2006  
3:41:33:000PM'

Msg 8114, Level 16, State 5, Procedure usp_tblCarrier_upd, Line 0
Error converting data type varchar to datetime.

I pass the datetime through as dbType.DateTime

please help
0
watherton
Asked:
watherton
  • 19
  • 15
  • 4
1 Solution
 
aprestoCommented:
Hi watherton,

Pass the date in yyyy/mm/dd format - this is a universal time format for sql server, you can use .ToString("YYYYMMDD") to get it there

Apresto
0
 
aprestoCommented:
>>>Jul 24 2006 10:02:35:530AM

actually the yyyymmdd wont work.  how is the above date value being created?
0
 
_Katka_Commented:
Hi, you can also try changing the SQL Server date format:

// month/day/year
SET DATEFORMAT mdy

// year/day/month
SET DATEFORMAT ydm

// year/month/day
SET DATEFORMAT ymd

regards,
Kate
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
wathertonAuthor Commented:
the dates are initially created using DateTime.Now

When i first save the record I created an auditDate using DateTime.Now

then when I update the record, I have a timestamp record that is set using the previous auditdate, and comparing it against a new DateTime.Now value
0
 
wathertonAuthor Commented:
hi kate, tried your set format date and still get the converting error.
0
 
aprestoCommented:
basically @AuditDate is a dateTime parameter and you are passing in a string that ( as far as Sql Server ) is converned has no resemblence to a date whatsoever - try parsing it into a Datetime object then passing it in.

You say that this string is derived from a DateTime object, just cut out the part where you convert it to a string
0
 
_Katka_Commented:
I see now, the problem here raises when you're using DateTime as a "string" parameter
it's much more better to pass it "as it is" parameter or variable depending on where you're standing:

1) from code in VS:

DateTime yourAuditDate = new DateTime(2006, 7, 24, 10, 2, 35); // Jul 24 2006 10:02:35:530AM in your case
DateTime yourTimeStamp = DateTime.Now; // Jun 15 2006 3:41:33:000PM in your case
SqlParameter[] yourParameters = new SqlParameter[2];
yourParameters[0] = new SqlParameter("@AuditDate", SqlDbTypes.DateTime, yourAuditDate);
yourParameters[1] = new SqlParameter("@TimeStamp", SqlDbTypes.DateTime, yourAuditDate);
..
other parameters
..
yourStoredProc.ExecuteNonQuery(yourConnection, CommandType.StoredProcedure, "usp_tblCarrier_upd",  yourParameters);

2) from T-SQL on server:

CREATE PROCEDURE usp_tblCarrier_upd (@AuditDate datetime)
AS
    // use @AuditDate in it's native form here
GO

regards,
Kate
0
 
wathertonAuthor Commented:
ok, perhaps I have not explained clearly.

when i create a client I set the auditDate in my param list as dbType.DateTime, DateTime.Now

then when I update a client i pull back the last audit date, which was set using DateTime now, I am storing all instances of dates as strings within my app. Again my auditDate is set using DateTime.Now and the TimeStamp param is populated with the retrieved auditDate.

0
 
aprestoCommented:
ok, but you are passing in a string, which is why you are being erroed, if you create another Datetime object with the date you wish to pass in you will solve the problem
0
 
wathertonAuthor Commented:
ok, maybe time for some code:

this is the class the holds the date values:

 public partial class AuditDetails : System.Web.UI.UserControl
    {
       
        public String CreatedDate
        {
            get { return txtCreatedDate.Value; }
            set { txtCreatedDate.Value = value; }
        }

        public String CreatedBy
        {
            get { return txtCreatedBy.Value; }
            set { txtCreatedBy.Value = value; }
        }

        public String LastModified
        {
            get { return txtLastModified.Value; }
            set { txtLastModified.Value = value; }
        }

        public String LastModifiedBy
        {
            get { return txtModifiedBy.Value; }
            set { txtModifiedBy.Value = value; }
        }

        public String StoppedDate
        {
            get { return txtStoppedDate.Value; }
            set { txtStoppedDate.Value = value; }
        }

        public String StoppedBy
        {
            get { return txtStoppedBy.Value; }
            set { txtStoppedBy.Value = value; }
        }
    }

this is the code that sets the value:

 ((AuditDetails)ucAudit).LastModified = Convert.ToString((DateTime)row["AuditDate"])

this is the update method

public void UpdateCarrier(int carrierId,string carrierCode,string carrierDesc,int productId,int clientId,string auditUserId,string auditDate)
        {
            Database db = CreateDatabase();

            StartTransaction();

            try
            {

                if (carrierId == 0)
                {
                    throw new DataException("The Carrier Id must be provided.");
                }

                using (DbCommand dbCommand = db.GetStoredProcCommand("usp_tblCarrier_upd"))
                {
                    db.AddInParameter(dbCommand, "CarrierID", DbType.String, carrierId);
                    db.AddInParameter(dbCommand, "CarrierCode", DbType.String, carrierCode);
                    db.AddInParameter(dbCommand, "CarrierDesc", DbType.String, carrierDesc);
                    db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productId);
                    db.AddInParameter(dbCommand, "ClientID", DbType.Int32, clientId);
                    db.AddInParameter(dbCommand, "AuditUserID", DbType.String, auditUserId);
                    db.AddInParameter(dbCommand, "AuditDate", DbType.DateTime, DateTime.Now);
                    db.AddInParameter(dbCommand, "TimeStamp", DbType.DateTime, auditDate);
                   

                    if (CurrentTransaction == null)
                    {
                        using (db.ExecuteReader(dbCommand)) { }
                    }
                    else
                    {
                        using (db.ExecuteReader(dbCommand, CurrentTransaction)) { }
                    }

                }

                CommitTransaction();

            }
            catch (SqlException error)
            {
                if (error.State == 1)
                {
                    throw new DuplicateNameException("Carrier already exists.");
                }
                else
                {
                    //HandleCriticalException(error);
                    throw new Exception(error.Message);
                }
            }

            catch (Exception error)
            {
                //HandleCriticalException(error);
                throw new Exception(error.Message);
            }

        }


and finally this is the sp that does the updating:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




ALTER PROCEDURE [dbo].[usp_tblCarrier_upd]
    @CarrierID INT,
    @CarrierCode NVARCHAR(10),
    @CarrierDesc NVARCHAR(50),
    @ProductID INT,
    @ClientID INT,
      @AuditUserID NVARCHAR(50) = NULL,
      @AuditDate DATETIME = NULL,
    @TimeStamp DATETIME
   
AS
/*
 *    PROCEDURE DESCRPTION
 *        Updates a row.
 *        AuditUserID will default to the current user if not supplied.
 *        @TimeStamp is used to avoid concurrency issues; it is set automatically as AuditDate by an insert ir update and must be
 *        supplied.  If it doesn't match, the record has been changed since the user selected it and the update will fail.
 *
 *    INPUT PARAMETERS:
 *        See list.
 *
 *    OUTPUT PARAMETERS:
 *        None.
 *
 *    RETURN CODES:
 *        0    Success.   Rows selected.
 *        n    An error occurred. Return @@error
 *
 *    VERSION HISTORY:
 *        02-jun-06    PSA    Original Version
 */
    --
    -- Local Variables
    --
    DECLARE @ErrNo INT            -- Used to hold the last @@ERROR.
    DECLARE @Rows INT            -- Used to hold the last @@ROWCOUNT.
      
      SET @AuditUserID = ISNULL(@AuditUserID, SYSTEM_USER)      
      SET @AuditDate = ISNULL(@AuditDate, GETDATE())
    BEGIN TRANSACTION
       
        UPDATE dbo.tblCarrier
        SET CarrierCode = @CarrierCode
        , CarrierDesc = @CarrierDesc
        , ProductID = @ProductID
        , ClientID = @ClientID
        , AuditUserID = @AuditUserID
        , AuditDate = @AuditDate
        WHERE CarrierID = @CarrierID
        AND AuditDate = @TimeStamp
        --
        -- Check error status
        --
        SELECT @ErrNo = @@ERROR, @Rows = @@ROWCOUNT
        IF (@ErrNo <> 0)
        BEGIN
            GOTO err_handler
           
        END
       
    COMMIT TRANSACTION
    --
    -- The End
    --
    RETURN 0

err_handler:
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION
    END
    RETURN @ErrNo


0
 
_Katka_Commented:
Ok, in this logic I would.. make some modification to your class (if possible ofcourse)

public partial class AuditDetails : System.Web.UI.UserControl
    {
       
        public DateTime CreatedDate
        {
            get { return Convert.ToDateTime(txtCreatedDate.Value); }
            set { txtCreatedDate.Value = value.ToString("mm/dd/yyyy"); }  // or whatver the format you want
        }

        public String CreatedBy
        {
            get { return txtCreatedBy.Value; }
            set { txtCreatedBy.Value = value; }
        }

        public DateTime LastModified
        {
            get { return Convert.ToDateTime(txtLastModified.Value); }
            set { txtLastModified.Value = value.ToString("mm/dd/yyyy"); }
        }

        public String LastModifiedBy
        {
            get { return txtModifiedBy.Value; }
            set { txtModifiedBy.Value = value; }
        }

        public DateTime StoppedDate
        {
            get { return Convert.ToDateTime(txtStoppedDate.Value); }
            set { txtStoppedDate.Value = value.ToString("mm/dd/yyyy"); }
        }

        public String StoppedBy
        {
            get { return txtStoppedBy.Value; }
            set { txtStoppedBy.Value = value; }
        }
    }

this is the code that sets the value:

 ((AuditDetails)ucAudit).LastModified = (DateTime)row["AuditDate"]); // no conversion to string needed anymore

this is the update method

public void UpdateCarrier(int carrierId,string carrierCode,string carrierDesc,int productId,int clientId,string auditUserId,DateTime auditDate)
        {
            Database db = CreateDatabase();

            StartTransaction();

            try
            {

                if (carrierId == 0)
                {
                    throw new DataException("The Carrier Id must be provided.");
                }

                using (DbCommand dbCommand = db.GetStoredProcCommand("usp_tblCarrier_upd"))
                {
                    db.AddInParameter(dbCommand, "CarrierID", DbType.String, carrierId);
                    db.AddInParameter(dbCommand, "CarrierCode", DbType.String, carrierCode);
                    db.AddInParameter(dbCommand, "CarrierDesc", DbType.String, carrierDesc);
                    db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productId);
                    db.AddInParameter(dbCommand, "ClientID", DbType.Int32, clientId);
                    db.AddInParameter(dbCommand, "AuditUserID", DbType.String, auditUserId);
                    db.AddInParameter(dbCommand, "AuditDate", DbType.DateTime, DateTime.Now);
                    db.AddInParameter(dbCommand, "TimeStamp", DbType.DateTime, auditDate); // I guess here was the major problem
                   

                    if (CurrentTransaction == null)
                    {
                        using (db.ExecuteReader(dbCommand)) { }
                    }
                    else
                    {
                        using (db.ExecuteReader(dbCommand, CurrentTransaction)) { }
                    }

                }

                CommitTransaction();

            }
            catch (SqlException error)
            {
                if (error.State == 1)
                {
                    throw new DuplicateNameException("Carrier already exists.");
                }
                else
                {
                    //HandleCriticalException(error);
                    throw new Exception(error.Message);
                }
            }

            catch (Exception error)
            {
                //HandleCriticalException(error);
                throw new Exception(error.Message);
            }

        }

regards,
Kate
0
 
wathertonAuthor Commented:
Hi Kate, I'll give it a try, thanks for the quick response, got to go live this afternoon :-(
0
 
_Katka_Commented:
You're welcome and.. *Thumbs up*

Note: generally it's better to handle DB parameters in their original form (strings are usually a bad choice due to localization and formatting issues:)

regards,
Kate
0
 
wathertonAuthor Commented:
I'm in the UK, will the date (string) "dd/mm/yyyy" work?
0
 
wathertonAuthor Commented:
ok, dd/mm/yyyy gives me

15 day - correct
41 month - incorrect
06 - correct
0
 
_Katka_Commented:
Sorry, "dd/MM/yyyy" :)

regards,
Kate
0
 
wathertonAuthor Commented:
ok, how about the time value as well
0
 
_Katka_Commented:
"dd/MM/yyyy hh:mm:ss"

regards,
Kate
0
 
wathertonAuthor Commented:
blast, still no joy, how frustrating is this

I still get the converting data type varchar to datetime error
0
 
_Katka_Commented:
at which point exactly you're getting this error ?

regards,
Kate
0
 
_Katka_Commented:
As far as I understand it:

1) you have some WebControl on your page which is responsible for updating your business object in DB
2) you fill some data and most important you fill some dates (some by hand others automatically)
3) you call the SP -> "usp_tblCarrier_upd" to update your business object with parameters provided
4) SP executes

at which point (assuming this scenario is correct) the exception raises ?

regards,
Kate
0
 
wathertonAuthor Commented:
within the sp
0
 
wathertonAuthor Commented:
comes out and displays correctly when retrieved, it's just passing back
0
 
_Katka_Commented:
Could you please post your SP here (or at least the portions if it's sensitive) ?

regards,
Kate
0
 
wathertonAuthor Commented:
i did further up the page :-)
0
 
wathertonAuthor Commented:
i used the auditDate and timestamp dates for checking if the record has changed since the user last retrieved it
0
 
_Katka_Commented:
Ok, I guess I need row types in a table updated..

        UPDATE dbo.tblCarrier
        SET CarrierCode = @CarrierCode
        , CarrierDesc = @CarrierDesc
        , ProductID = @ProductID
        , ClientID = @ClientID
        , AuditUserID = @AuditUserID
        , AuditDate = @AuditDate
        WHERE CarrierID = @CarrierID
        AND AuditDate = @TimeStamp

I mean these rows: CarrierCode, ProductID, ClientID, AuditUserID, AuditDate

because it seems that at least column AuditDate is of a type varchar (internally in DB)

regards,
Kate
0
 
wathertonAuthor Commented:
all dates in table are datetime.

the sp works 100% because it has been tested outside of VS.

i need to pull the date out of the db (auditDate) and send it back exactly as it came.

so:

2006-07-24 14:13.07.743

should be read as such by ((AuditDetails)ucAudit).LastModified = (DateTime)row["AuditDate"];
0
 
_Katka_Commented:
Ok so.. it's the format in your recieved DataSet (DataTable) in column AuditDate because:

(DateTime)row["AuditDate"]  // where row AuditDate is of a type DbType.VarChar, SqlDbType.VarChar or typeof(String)

1) check your virtual client DataSet / DataTable (if there's correct type)
2) try to convert it differently such as:

((AuditDetails)ucAudit).LastModified = Convert.ToDateTime(row["AuditDate"].ToString());

regards,
Kate
0
 
wathertonAuthor Commented:
my dataset is populated using a select * statement based on a filter.

can not implicity convert string to System.DateTime when using ((AuditDetails)ucAudit).LastModified = Convert.ToDateTime(row["AuditDate"].ToString());
0
 
_Katka_Commented:
Well ok, after eliminating all the possibilities the problem lies, with all the confidence, there (as marked).
Try to set a breakpoint on that line and check in watches if it's possible to run this line virtually..
and check for the error, also check for properties of:

dbCommand.Parameters["AuditDate"]
dbCommand.Parameters["TimeStamp"]

because inconsistencies are expected there as well..

...
 using (DbCommand dbCommand = db.GetStoredProcCommand("usp_tblCarrier_upd"))
                {
                    db.AddInParameter(dbCommand, "CarrierID", DbType.String, carrierId);
                    db.AddInParameter(dbCommand, "CarrierCode", DbType.String, carrierCode);
                    db.AddInParameter(dbCommand, "CarrierDesc", DbType.String, carrierDesc);
                    db.AddInParameter(dbCommand, "ProductID", DbType.Int32, productId);
                    db.AddInParameter(dbCommand, "ClientID", DbType.Int32, clientId);
                    db.AddInParameter(dbCommand, "AuditUserID", DbType.String, auditUserId);
here ->         db.AddInParameter(dbCommand, "AuditDate", DbType.DateTime, DateTime.Now);
or here ->     db.AddInParameter(dbCommand, "TimeStamp", DbType.DateTime, auditDate);
...

regards,
Kate
0
 
wathertonAuthor Commented:
thanks for all your help Kate, however stepping through reveals no problems.

.... Ok


I populate the records based on a DataView

I think, not sure on this, that the update sp uses the miliseconds, however i don't pull out, or return the miliseconds. This does not work either "dd/MM/yyyy hh:mm:ss:ms")
0
 
_Katka_Commented:
Ok.. you can format datetime to use miliseconds.. just like:

"dd/MM/yyyy hh:mm:ss.fff"

f - fraction of second (three most significant digits)

regards,
Kate
0
 
wathertonAuthor Commented:
24 hrs clock?
0
 
_Katka_Commented:
I'm from Czech Republic.. sorry :)

regards,
Kate
0
 
wathertonAuthor Commented:
you will not believe this - bloody miliseconds and 24 hr clock

this resolved it

ToString("dd/MM/yyyy HH:mm:ss.fff")

Thank you very very much
0
 
_Katka_Commented:
Well I don't believe that :P but I was positive it will be some kind of banality :))

Motto: The most absurd bugs are the toughest to find

regards,
Kate

0
 
wathertonAuthor Commented:
yep
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 19
  • 15
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now