Click here to Skip to main content
14,698,201 members
Please Sign up or sign in to vote.
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 = &quot;INSERT INTO Master (cDate,Emp_Number,Emp_Name,Work_Location,Mode,OTMode,Normal_OT,DayOff_OT)&quot; +
&quot; VALUES ( &#39;&quot; + Dtpicker + &quot;&#39;,&quot; + cmbEmpno + &quot;, &#39;&quot; + txtEmpname + &quot;&#39;,&#39;&quot; + txtLocation + &quot;&#39;,&#39;&quot; + cmbMode + &quot;&#39;, &#39;&quot; + cmbOtmode + &quot;&#39;,&quot; + txtNormalOT + &quot;,&quot; + txtDayofOT + &quot;)&quot;;
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 = &quot;SELECT * FROM Master ORDER BY Emp_Number&quot;;
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[&quot;Master&quot;].ImportRow(row);
}
rptreport objreport = new rptreport();
objreport.SetDataSource(ds);

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

crystalReportViewer1.ReportSource = objreport;
crystalReportViewer1.Show();</pre>
Posted
Updated 10-Jul-11 22:35pm
v3
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

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
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900