Insert query with datetime

Hi, I am working on MSSQL, C#, ASP.Net 1.1

I have DataTime in variable
DateTime dtContactDate = txtContactDate.SelectedDate;
having the value  = 7/25/2007


I create a insert query string at runtime so I get the insert query in the form

INSERT INTO SleepingCustomerContactDates([CustomerId],[ContactedDate],[Remarks])Values (430,'25/07/2007 10:33:18','Test');

Now, I changed above DateTime to string as below

string strContactDate = dtContactDate.ToShortDateString();

I get the value "25/07/2007"

and then I get my inserts statement generated as below

INSERT INTO SleepingCustomerContactDates([CustomerId],[ContactedDate],[Remarks])Values (430,'25/07/2007','Test');


In both the cases when I execute the insert query, I get the error below:

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.



In C#, I create the query as below

strQuery = strQuery+"INSERT INTO SleepingCustomerContactDates([CustomerId],[ContactedDate],[Remarks])"+
"Values ("+strCustomerId+",'"+strContactDate+"','"+strRemarks+"');";

How to solve the problem

Please help
tia_kamakshiAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
INSERT INTO SleepingCustomerContactDates([CustomerId],[ContactedDate],[Remarks])Values (430,'2007-07-25','Test');

INSERT INTO SleepingCustomerContactDates([CustomerId],[ContactedDate],[Remarks])Values (430,'25-July-2007','Test');

or

SET DATEFORMAT dmy
INSERT INTO SleepingCustomerContactDates([CustomerId],[ContactedDate],[Remarks])Values (430,'25/07/2007','Test');



0
All Courses

From novice to tech pro — start learning today.