inserting date to sql server 2000

Question

0.00/5 (No votes)

See more:

hi all,

i am having some issues in saving the date in sql server 2000.
from my application i have save the data with date format "mm/dd/yyyy"-"07-11-2011". I have checked it's sending the data with this format to the sql server.

but when i check in the sql server it save as "2011-11-07 00:00:00.000" this format.

when i'm retrieving from the report it comes as "2011-yyyy",11-mm"","07-dd" for this issue what can i do?

please any one help me in this case...

below are the codes....

<b>add to the sql server</b>
<pre>public void AddTimesheet(DateTime Dtpicker, int cmbEmpno, string txtEmpname, string txtLocation, string cmbMode, string cmbOtmode, int txtNormalOT, int txtDayofOT)
{
string sqlstr;
sqlstr = "INSERT INTO Master (cDate,Emp_Number,Emp_Name,Work_Location,Mode,OTMode,Normal_OT,DayOff_OT)" +
" VALUES ( '" + Dtpicker + "'," + cmbEmpno + ", '" + txtEmpname + "','" + txtLocation + "','" + cmbMode + "', '" + cmbOtmode + "'," + txtNormalOT + "," + txtDayofOT + ")";
con = new SqlConnection(clsConnection.strconnection);
con.Open();

da = new SqlDataAdapter();
da.InsertCommand = new SqlCommand();
da.InsertCommand.Connection = con;
da.InsertCommand.CommandText = sqlstr;
da.InsertCommand.CommandType = CommandType.Text;
da.InsertCommand.ExecuteNonQuery();

DateTime datetimepic = Convert.ToDateTime(Dtpicker.Value.Date);
int empno = Convert.ToInt32(cmbEmpno.Text);
string empname = txtEmpname.Text;
string emplocation = txtLocation.Text;
string mode = cmbMode.Text;
string otmode = cmbOtmode.Text;
int normalot = Convert.ToInt32(txtNormalOT.Text);
int dayoffot = Convert.ToInt32(txtDayofOT.Text);

Classes.clsAttendance att = new testing.Classes.clsAttendance();
att.AddTimesheet(datetimepic, empno, empname, emplocation, mode, otmode, normalot, dayoffot);</pre>

<b>report code</b>

<pre>DateTime Datefrom = Convert.ToDateTime(dateFrom.Value.Date);
DateTime Dateto =Convert.ToDateTime( dateTo.Text);
string Location1 = Location.Text;
string Mode1 = Mode.Text;
string Otmode = otmode.Text;

string sqlstr = "SELECT * FROM Master ORDER BY Emp_Number";
con = new SqlConnection(clsConnection.strconnection);
con.Open();
DataSet ds = new DtReport();
DataTable dt = new DataTable();
SqlDataAdapter da = default(SqlDataAdapter);

da = new SqlDataAdapter(sqlstr, con);
da.Fill(dt);
foreach (DataRow row in dt.Rows)
{
ds.Tables["Master"].ImportRow(row);
}
rptreport objreport = new rptreport();
objreport.SetDataSource(ds);

objreport.SetParameterValue("Datefrom", dateFrom.Value.Date);
objreport.SetParameterValue("Dateto", dateTo.Text);
objreport.SetParameterValue("Location", Location1);
objreport.SetParameterValue("Mode", Mode1);
objreport.SetParameterValue("Otmode", Otmode);

crystalReportViewer1.ReportSource = objreport;
crystalReportViewer1.Show();</pre>

Posted 10-Jul-11 21:33pm

ruzan hizar

Updated 10-Jul-11 21:35pm

Uday P.Singh

v3

Add a Solution

Comments

ruzan hizar 11-Jul-11 3:45am

while i'm saving the date from my form it send to sql server db as this format "mm/dd/yyyy"

but it save in the sql server as this format "yyyy/mm/dd"

when i'm selecting that date from crystal report the month column comes as date and date column as month.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Vipin Patil · Answer 1 · 2011-07-11T00:19:00

Solution 1

XML

Try this query to get records from database
SELECT eventID,eventName, eventPlace,eventTime,CONVERT (varchar(150), eventDate,<u>103</u>) As eventDate
FROM         EventTimeTable

For more Date Format please follow this link

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Posted 11-Jul-11 0:19am

Vipin Patil