[2.0] Date Problems inserting into SQL Server

[effekt26]

Hey everyone,

Im having trouble inserting a date into sql server.

I get the following error when I run my stored procedurem which i am guessing is returned from the SQL Server

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

I am in australia, and as such, my date formats as dd/mm/yyyy. Below is what happens with my date object.

Request.CreateDate (Request is class, CreateDate is the date the request was created, with DateTime object type.)

Request.CreateDate = Convert.ToDateTime("22/11/2007");

My sql database for the create_date field is of type DateTime.

When I am debugging my code, and I hover my mouse over the Request.CreateDate property, it gives me the date information as Day 22, 11 Month, year 2007, so it is creating the date object correctly, however, when I insert it into the db, I get the afformentioned error, and I am assuming its reading it in mm/dd/yyyy format, and, of course, there is no 22nd month.

Any help on this would be infinitely appreciated.

Cheers,
Justin

[fret]

I think date values in sql is always save in MM/DD/YYYY(YY) format, though just a suggestion why don't you save that in its default and when you display you just format it to what you used to.

[effekt26]

how do i do that?

all of my date objects in my .net app are valid date object, and I cant pass a string in the form of 'MM/dd/yyyy HH:mm' as i get an error unable to convert string to datetime....

thanks for the help, I have been trying to do that anyways, but just wont go... :S

[jmcilhinney]

I'm in Australia too and I never have any problem with dates, and nor shoudl anyone else. DateTime object shave no format, so if you stick to using DateTime objects then no problem will ever arise. Format is only an issue when DISPLAYING a date as a string. There should not be any reason to convert a DateTime object to or from a String if you getting it from or saving it to a database.

Show us your sproc and how you're calling it, as you should have in the first place.

[effekt26]

I'm in Australia too and I never have any problem with dates, and nor shoudl anyone else. DateTime object shave no format, so if you stick to using DateTime objects then no problem will ever arise. Format is only an issue when DISPLAYING a date as a string. There should not be any reason to convert a DateTime object to or from a String if you getting it from or saving it to a database.

Show us your sproc and how you're calling it, as you should have in the first place.

Hey, After more playing around, I am now going to use the SQL Profiler to see what is actually getting sent to my sql server.

I knew it should not have mattered, but was not sure, as when I would execute the sproc withing sql management studio, '22/11/2007' would error, when '11/22/2007' would not...

here is my sproc

CREATE PROCEDURE [dbo].[sp_UpdateRequest]

	@request_id INT,
	@request_client_id INT, 
	@request_description VARCHAR(150), @request_date DATETIME, 
	@request_edit_date DATETIME, @request_status VARCHAR(25), @request_approve_date DATETIME,
	@request_activity_code VARCHAR(7), @request_department_code VARCHAR(3), @request_participants INT, 
	@request_activity_date DATETIME, @request_activity_due_date DATETIME,
	@request_company_id INT, @request_company_code INT,
	@request_notes VARCHAR(2000)

AS
BEGIN

	UPDATE invoice_requests
	
	SET request_client_id = @request_client_id,	request_description = @request_description,
	request_date = @request_date, request_edit_date = @request_edit_date, 
	request_status = @request_status, request_approve_date = @request_approve_date, 
	request_activity_code = @request_activity_code, 
	request_department_code = @request_department_code,
	request_participants = @request_participants, 
	request_activity_date = @request_activity_date, 
	request_activity_due_date = @request_activity_due_date, 
	request_company_code = @request_company_code, request_company_id = @request_company_id,
	request_notes = @request_notes

	WHERE request_id = @request_id

END
GO

my UpdateRequest method:

public static void UpdateRequest(InvoiceRequest Request) {

			DataAccess _da = new DataAccess(DataAccess.SqlServer.Local);

			string _sql = "EXECUTE sp_UpdateRequest @request_id, @request_client_id, @request_description, @request_date, @request_edit_date, @request_status, @request_approve_date, @request_activity_code, @request_participants, @request_activity_date, @request_activity_due_date, @request_company_id, @request_company_code, @request_notes";

			// Default Approval Date
			DateTime _approvalDate = new DateTime(1970, 1, 1);

			Queue<DataParameter> _params = new Queue<DataParameter>();
			_params.Enqueue(new DataParameter("@request_id", Request.RequestId, SqlDbType.Int));
			_params.Enqueue(new DataParameter("@request_client_id", Request.ClientId, SqlDbType.Int));
			_params.Enqueue(new DataParameter("@request_description", Request.Description, SqlDbType.VarChar));
			_params.Enqueue(new DataParameter("@request_date", new DateTime(2007,11,6), SqlDbType.DateTime));
			_params.Enqueue(new DataParameter("@request_edit_date", new DateTime(2007, 11, 6), SqlDbType.DateTime));
			_params.Enqueue(new DataParameter("@request_status", Request.Status, SqlDbType.VarChar));
			_params.Enqueue(new DataParameter("@request_approve_date", new DateTime(2007, 11, 6), SqlDbType.DateTime));
			_params.Enqueue(new DataParameter("@request_activity_code", Request.ActivityCode, SqlDbType.VarChar));
			_params.Enqueue(new DataParameter("@request_department_code", Request.DepartmentCode, SqlDbType.VarChar));
			_params.Enqueue(new DataParameter("@request_participants", Request.Participants, SqlDbType.Int));
			_params.Enqueue(new DataParameter("@request_activity_date", new DateTime(2007, 11, 6), SqlDbType.DateTime));
			_params.Enqueue(new DataParameter("@request_activity_due_date", new DateTime(2007, 11, 6), SqlDbType.DateTime));
			_params.Enqueue(new DataParameter("@request_company_id", Request.CompanyId, SqlDbType.Int));
			_params.Enqueue(new DataParameter("@request_company_code", Request.CompanyCode, SqlDbType.Int));
			_params.Enqueue(new DataParameter("@request_notes", Request.Notes, SqlDbType.VarChar));

			_da.ExecuteQuery(_sql, _params);


		}

and my ExecuteQuery method

public void ExecuteQuery(string SqlQuery, Queue<DataParameter> Parameters) {

            SqlCommand _cmd = null;

            using (SqlConnection _conn = new SqlConnection(_connectionString)) {

                try {
					
					_cmd = new SqlCommand(SqlQuery, _conn);

					foreach(DataParameter _parameter in Parameters) {

						switch (_parameter.DataType) {

							case SqlDbType.Int:
								_cmd.Parameters.Add(_parameter.Name, _parameter.DataType).Value = Convert.ToInt32(_parameter.Value);
								break;

							case SqlDbType.VarChar:
								_cmd.Parameters.Add(_parameter.Name, _parameter.DataType).Value = _parameter.Value.ToString();
								break;

							case SqlDbType.DateTime:
								DateTime _convertedDate = Convert.ToDateTime(_parameter.Value);
								//string _dateString = _convertedDate.ToString("MM/dd/yyyy HH:mm");
								_cmd.Parameters.Add(_parameter.Name, _parameter.DataType).Value = _convertedDate;
								break;

							default:
								_cmd.Parameters.Add(_parameter.Name, _parameter.DataType).Value = _parameter.Value;
								break;

						}

					}

					_conn.Open();
					_cmd.ExecuteNonQuery();

                    return;

                } catch (Exception ex) {

					throw new Exception(ex.Message);

                }

            }

        }

i wonder why sql server would not accept the australian date format when it asks for parameters when executing the procedure...

Thanks again,
Justin

[jmcilhinney]

It looks like the problem is here:

							case SqlDbType.DateTime:
								DateTime _convertedDate = Convert.ToDateTime(_parameter.Value);
								//string _dateString = _convertedDate.ToString("MM/dd/yyyy HH:mm");
								_cmd.Parameters.Add(_parameter.Name, _parameter.DataType).Value = _convertedDate;
								break;

Why do you have to convert your parameter value to a DateTime object? Is it not a DateTime object already?

[effekt26]

Sorry, I should have mentioned, My DataParameter class takes the following in the constructior..

[code]public DataParameter(string Name, object Value, SqlDbType DataType) { }[/quote]

public class DataParameter {

        private string _name;
        private object _value;
        private SqlDbType _dataType;

        public string Name {
            get { return _name; }
            set { _name = value; }
        }

        public object Value {
            get { return _value; }
            set { _value = value; }
        }

        public SqlDbType DataType {
            get { return _dataType; }
            set { _dataType = value; }
        }

        public DataParameter(string Name, object Value, SqlDbType DataType) {

            _name = Name;
            _value = Value;
            _dataType = DataType;
        }

    }

hope this helps clear that up.

Cheers,
Justin

[effekt26]

and this is the output from my SQL Profile session...

call me blind, but I cannot see anything that is causing this error, or anything that is trying to convert an integer to a datetime.

note, have broken it up, as it was all on slab of text...

exec sp_executesql 
N'EXECUTE sp_UpdateRequest 
@request_id, 
@request_client_id, 
@request_description, 
@request_date, 
@request_edit_date, 
@request_status, 
@request_approve_date, 
@request_activity_code, 
@request_participants, 
@request_activity_date, 
@request_activity_due_date, 
@request_company_id, 
@request_company_code, 
@request_notes',

N'@request_id int,
@request_client_id int,
@request_description varchar(8000),
@request_date datetime,
@request_edit_date datetime,
@request_status varchar(5),
@request_approve_date datetime,
@request_activity_code varchar(8000),
@request_department_code varchar(8000),
@request_participants int,
@request_activity_date datetime,
@request_activity_due_date datetime,
@request_company_id int,
@request_company_code int,
@request_notes varchar(8000)',

@request_id=5,
@request_client_id=1,
@request_description='',
@request_date=''2007-11-22 16:34:32:997'',
@request_edit_date=''2007-11-22 16:34:32:997'',
@request_status='Draft',
@request_approve_date=''1970-01-01 00:00:00:000'',
@request_activity_code='',
@request_department_code='',
@request_participants=0,
@request_activity_date=''2007-11-29 00:00:00:000'',
@request_activity_due_date=''2007-11-23 00:00:00:000'',
@request_company_id=0,
@request_company_code=1,
@request_notes=''

Thanks again,
Justin