Solved

SQL 2005 dates

Posted on 2006-07-24
38
278 Views
Last Modified: 2008-01-09
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
Comment
Question by:watherton
  • 19
  • 15
  • 4
38 Comments
 
LVL 23

Expert Comment

by:apresto
ID: 17166203
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
 
LVL 23

Expert Comment

by:apresto
ID: 17166214
>>>Jul 24 2006 10:02:35:530AM

actually the yyyymmdd wont work.  how is the above date value being created?
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 17166257
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
 

Author Comment

by:watherton
ID: 17166258
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
 

Author Comment

by:watherton
ID: 17166284
hi kate, tried your set format date and still get the converting error.
0
 
LVL 23

Expert Comment

by:apresto
ID: 17166313
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
 
LVL 10

Expert Comment

by:_Katka_
ID: 17166415
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
 

Author Comment

by:watherton
ID: 17166712
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
 
LVL 23

Expert Comment

by:apresto
ID: 17166752
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
 

Author Comment

by:watherton
ID: 17166763
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
 
LVL 10

Expert Comment

by:_Katka_
ID: 17166919
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
 

Author Comment

by:watherton
ID: 17166983
Hi Kate, I'll give it a try, thanks for the quick response, got to go live this afternoon :-(
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 17167019
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
 

Author Comment

by:watherton
ID: 17167036
I'm in the UK, will the date (string) "dd/mm/yyyy" work?
0
 

Author Comment

by:watherton
ID: 17167054
ok, dd/mm/yyyy gives me

15 day - correct
41 month - incorrect
06 - correct
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 17167067
Sorry, "dd/MM/yyyy" :)

regards,
Kate
0
 

Author Comment

by:watherton
ID: 17167110
ok, how about the time value as well
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 17167128
"dd/MM/yyyy hh:mm:ss"

regards,
Kate
0
 

Author Comment

by:watherton
ID: 17167131
blast, still no joy, how frustrating is this

I still get the converting data type varchar to datetime error
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 10

Expert Comment

by:_Katka_
ID: 17167204
at which point exactly you're getting this error ?

regards,
Kate
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 17167227
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
 

Author Comment

by:watherton
ID: 17167230
within the sp
0
 

Author Comment

by:watherton
ID: 17167239
comes out and displays correctly when retrieved, it's just passing back
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 17167251
Could you please post your SP here (or at least the portions if it's sensitive) ?

regards,
Kate
0
 

Author Comment

by:watherton
ID: 17167518
i did further up the page :-)
0
 

Author Comment

by:watherton
ID: 17167631
i used the auditDate and timestamp dates for checking if the record has changed since the user last retrieved it
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 17167633
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
 

Author Comment

by:watherton
ID: 17167714
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
 
LVL 10

Expert Comment

by:_Katka_
ID: 17167890
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
 

Author Comment

by:watherton
ID: 17168073
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
 
LVL 10

Expert Comment

by:_Katka_
ID: 17168220
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
 

Author Comment

by:watherton
ID: 17168276
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
 
LVL 10

Accepted Solution

by:
_Katka_ earned 500 total points
ID: 17168373
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
 

Author Comment

by:watherton
ID: 17168619
24 hrs clock?
0
 
LVL 10

Expert Comment

by:_Katka_
ID: 17168657
I'm from Czech Republic.. sorry :)

regards,
Kate
0
 

Author Comment

by:watherton
ID: 17168683
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
 
LVL 10

Expert Comment

by:_Katka_
ID: 17168719
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
 

Author Comment

by:watherton
ID: 17168908
yep
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now