Click here to Skip to main content
15,890,741 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have one maskedtextbox in windows application 2010 in which i have entered date in dd/mm/yyyy format and converted it in mm/dd/yyyy format(because sql 2008 database accept date in mm/dd/yyyy format) to submit it successfully in sql 2008 database as follows:-for eg 25/09/2013NOTE :-MY SYSTEM DATE IS ALSO IN DD/MM/YYYY FORMAT
C#
 private void btn_submit_Click(object sender, EventArgs e)
        {
            
            
            SqlCommand cmd = new SqlCommand("INSERT INTO date (discharge_date) values (CONVERT(smalldatetime, '" + maskedtxbx_dischargedate.Text + "', 103))", con);//date is my table name and discharge_date is column name in which i have to save dischargedate.
 

            cmd.Parameters.AddWithValue("@discharge_date", maskedTextBox1.Text);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

            MessageBox.Show("Records inserted");

        }
private void btn_retreive_Click(object sender, EventArgs e)
        {
            SqlDataAdapter da = new SqlDataAdapter("select * from date where discharge_date= '"+maskedTextBox1.Text+"'", con);
            
            SqlCommandBuilder cmd = new SqlCommandBuilder(da);
            da.Fill(dtusers);
            dataGridView1.DataSource = dtusers;
            dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells);
      
        }

Now comes the second part i.e when MY SYSTEM DATE FORMAT IS IN MM/DD/YYYY FORMAT I have entered date in dd/mm/yyyy format in the same maskedtextbox and after that i have clicked the retrieve button so that the dischargedate entered in maskedtextbox should match with the date saved in sql database and then the matching dischargedates should show in gridview.

But when I enter 25/09/2013 it retrieves the results in gridview in mm/dd/yyyy format i.e 09/24/2013


So my question is: what is the role of SYSTEM DATE FORMAT in Microsoft Visual studio 2010 Windows application and sql 2008 databases when we are already using DATETIME CONVERT FUNCTIONS
AS 103. WHAT TO DO WHEN SYSTEM DATE FORMAT IS IN MM/DD/YYYY FORMAT?
[EDIT]Do not SHOUT![/EDIT]
Posted
Updated 2-Oct-13 22:31pm
v5
Comments
coded007 26-Sep-13 6:16am    
you can use convert(VARCHAR(20),getdate(),3) so that it will insert not a problem
sachin4dotnet 26-Sep-13 6:20am    
I think you have to convert in Select query like this :
"select * from date where discharge_date = CONVERT(smalldatetime, '"+maskedTextBox1.Text+"', 103) "
alanmaster 26-Sep-13 6:48am    
Thanks job is done. your code worked.
SqlDataAdapter da = new SqlDataAdapter("select * from date where discharge_date= CONVERT(smalldatetime, '" + maskedTextBox1.Text + "', 103) ", con);
Once again thanks.
alanmaster 3-Oct-13 3:25am    
But this solution only work when the system date is also in dd/mm/yyyy format.What to do when the system or the server has the format of mm/dd/yyyy format and it is not ok to chage that format.
sachin4dotnet 3-Oct-13 4:00am    
generally I use this for my help in converting date to varchar in SQL

http://www.sqlusa.com/bestpractices/datetimeconversion/[^]

you can find all type of conversions there..

1 solution

I think you have to convert in Select query like this :
C#
"select * from date where discharge_date = CONVERT(smalldatetime, '" + maskedTextBox1.Text + "', 103) "


Answer :
C#
SqlDataAdapter da = new SqlDataAdapter("select * from date where discharge_date= CONVERT(smalldatetime, '" + maskedTextBox1.Text + "', 103) ", con);
 
Share this answer
 

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